worksheet protection

[deedee88]

Hi,

I have a password protected worksheet that has hidden rows. My issue is that if a user saves this file under another name the rows are no longer hidden.

Is there anyway around this?

Any help will be greatly appreciated!

 

[crimson_b1ade]

when you protect your sheet there should be a selection where you can deny the selection of locked cells (this is in the box right before you enter a password). this should make it a bit harder (but of course, not impossible) to copy and paste data to an unprotected sheet for their viewing.

 

[crimson_b1ade]

Just as a follow-up...if there's any information that you don't want to be seen by others you send the spreadsheet to or that others have access to, then that information shouldn't be in the spreadsheet to begin with. There are many ways to get around worksheet protection, hidden rows etc...so if you don't want it to be seen...then...take it out.

 

[deedee88]

Thank you!

 

[XLXRider]

You might like this idea. I have had problems with people saving a copy of a file, then complaining that the file doesn't work. What it is, is everyone has to have his or her copy of the file. They make the input, and then save their own file... so you end up with 22 copies that have the right data entries, but not in the same file where they're supposed to be! So I force the user to save it only where it came from by putting this in the This Workbook module:

Private Sub Workbook_Open()
With Application
.CommandBars("File").Enabled = False
.CommandBars("Tools").Enabled = False
.CommandBars("View").Enabled = False
.CommandBars("Standard").Enabled = False
.CommandBars("Edit").Enabled = False
End With
MsgBox ("You are prevented from using any ""File Save"" or ""File Save As"" commands, ""Edit"", ""View"", and ""Tools""." & Chr(13) & "These options will return when you close this workbook using the ""x"" in the upper right hand corner." & Chr(13) & "Your work will be saved if you use the ""x"" but you must answer the question with ""YES.""")
Application.Goto Reference:="Shift1Home"
MsgBox ("PLEASE READ THE ""INSTRUCTIONS"" TAB!")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.CommandBars("File").Enabled = True
.CommandBars("Tools").Enabled = True
.CommandBars("View").Enabled = True
.CommandBars("Standard").Enabled = True
.CommandBars("Edit").Enabled = True
End With
End Sub

Of course, you can make the message say whatever you like... I leave it clickable to close, that way the user can't give the excuse that he didn't have time to read the message. In this particular example, I also tell the user to go to the "Instructions" tab in the file to read how it works.

This will keep the file where you want it unless, of course, your users are more sophisticated than mine are! So far, none of my users have figured out how to run code from within VBE; and if you feel the need, you can password the code modules, too...

HTH, I thought you might be interested in another option!

Regards,
XLXRider

 

[crimson_b1ade]

what if the user disabled macros?

You might like this idea. I have had problems with people saving a copy of a file, then complaining that the file doesn't work. What it is, is everyone has to have his or her copy of the file. They make the input, and then save their own file... so you end up with 22 copies that have the right data entries, but not in the same file where they're supposed to be! So I force the user to save it only where it came from by putting this in the This Workbook module:

Private Sub Workbook_Open()
With Application
.CommandBars("File").Enabled = False
.CommandBars("Tools").Enabled = False
.CommandBars("View").Enabled = False
.CommandBars("Standard").Enabled = False
.CommandBars("Edit").Enabled = False
End With
MsgBox ("You are prevented from using any ""File Save"" or ""File Save As"" commands, ""Edit"", ""View"", and ""Tools""." & Chr(13) & "These options will return when you close this workbook using the ""x"" in the upper right hand corner." & Chr(13) & "Your work will be saved if you use the ""x"" but you must answer the question with ""YES.""")
Application.Goto Reference:="Shift1Home"
MsgBox ("PLEASE READ THE ""INSTRUCTIONS"" TAB!")
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.CommandBars("File").Enabled = True
.CommandBars("Tools").Enabled = True
.CommandBars("View").Enabled = True
.CommandBars("Standard").Enabled = True
.CommandBars("Edit").Enabled = True
End With
End Sub

Of course, you can make the message say whatever you like... I leave it clickable to close, that way the user can't give the excuse that he didn't have time to read the message. In this particular example, I also tell the user to go to the "Instructions" tab in the file to read how it works.

This will keep the file where you want it unless, of course, your users are more sophisticated than mine are! So far, none of my users have figured out how to run code from within VBE; and if you feel the need, you can password the code modules, too...

HTH, I thought you might be interested in another option!

Regards,
XLXRider

 

[Evagrius Ponticus]

XLSRIDER - what if the user doesn't close the workbook w/the code but just deactivates it and activates another workbook not related? The disabled items will not be enabled on the active workbook, right?

 

[XLXRider]

BlackTiger,
Good question. What I've found is that if you have two different Excels open (not two files on the same click of Excel, but another instance of Excel) that only the one with the workbook with the macros posted here will be affected. However, if you open a few files, then open the one with the code listed above in it, every open file is affected until you close the one with the code in it. Of course, if you open the coded file first, you won't be able to open any others. Not with the menu, anyhow.

Crimson_Blade,
Yep, that's a possibility... I did have that issue a couple of years ago. One user smugly showed me how he was able to do as he pleased by setting his macro security higher, then disabled macros. Soooo, the next time he opened the file (which I had deleted his "copy of" version) he found that the file checked to see if macros were enabled. If he clicked "disable macros" the file gave him a "so sorry, but you must enable macros" message and then closed itself. hee hee hee... I sorta flipped him the ole bird on that one. I should still have a copy of that file around somewhere, if you're interested in the code I used to do that. I can't recall it off the top of my head, they say that er, um, something is the first thing to go as you age but I can't remember what that is????

Of course, if the code isn't protected by a password, the user - if he's savvy enough - can just go "view code" and highlight the code to enable the disabled options, and then run it from within the VBE.

Regards,
XLX

 

[Smitty]

Of course, if the code isn't protected by a password, the user - if he's savvy enough - can just go "view code" and highlight the code to enable the disabled options, and then run it from within the VBE.

Yes, but if you protect the VBE Project, then 99% of users won't be able to get in.