Password protecting hidden cells

[Imran J Khan]

Hi,
I have a client that has inventory on a single excel file on a office
network. They want to be able to hide some cells from some users and not from
others. They have mentioned they have group-level security setup on their
network, so this could be used. What I am trying to do is hide some
columns/rows and then password protecting the sheet, and also tried to
password protect the range as well, but it does not seem to work. I have to
unprotect the sheet to hide/unhide the column, then I have to remember to
protect it again after. This can obvious lead to human error where the user
forgets to protect the sheet after using. I would like to use group level
protection, so one group (Accountants) can see the dollar value of inventory,
while another group (Floor Managers) cannot see the dollar values. I need
someone to give me a guide on how to do this.

Thanks in Advance
Imran

 

[Shane Devenshire]

Hi,

I think your solution will involve VBA.

Here is an example of a Before Save event which hides some rows and columns
and protects the spreadsheet with a password:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Columns("C:E").EntireColumn.Hidden = True
Rows("4:9").EntireRow.Hidden = True
ActiveSheet.Protect Password:="password"
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Imran J Khan]

Thanks Shane, I had come to same conclusion, but because of costs, the client
had prefered a quicker solution. The workbook has 55 worksheets, so this
would take a some time to implement. Thanks all the same.
Imran