hannah has attended:
Excel Advanced course
Password Protection / Restricting Users
I have a multi-tab workbook with multiple users. There is a password to open the workbook. Some sections of sheets within the workbook are grouped and you need to know the password to un-group these sections. I've used the same password for each sheet that has grouped sections. A collection of people know the individual sheet password to be able to un-group sections, and re-group and password protect again when saving. The purpose of this is to hide confidential sections of information that not all users are privy to. However, on some sheets I would like to add another level of password protection that allows only me to edit certain sections. I have tried to do this by allowing users to edit different ranges but my problem is that doesn't work with grouped sections. When the overall sheet is protected you can't un-group unless the protection is removed from the sheet.
Secondly, I would like to add a macro with a button that re-groups and password protects all relevant sheets. I've set this up by simply recording a macro but then it doesn't run because the sheets are password protected?!
It would be great to get some help with this.
Thanks,
Hannah
RE: Password Protection / Restricting Users
Hello Hanna,
Thank you for your question. 'Allow users to edit ranges' only applies to visible ranges of cells, not to hidden or grouped columns or rows. These can only be protected by protecting the worksheet.
As for the macro question, here are two separate macros: the first one hides columns and then protects the sheets (I've tried grouping and collapsing but the collapse bit doesn't record, so I would recommend hiding them instead).
The second unprotects the sheets then unhides the hidden columns.
Sub ProtectSheets()
Sheets("sheet1").Columns("C:E").EntireColumn.Hidden = True
Sheets("sheet2").Columns("C:E").EntireColumn.Hidden = True
Sheets("Sheet1").Protect Password:="a"
Sheets("Sheet2").Protect Password:="b"
End Sub
Sub UnprotectSheets()
Sheets("Sheet1").Unprotect Password:="a"
Sheets("Sheet2").Unprotect Password:="b"
Sheets("sheet1").Columns("C:E").EntireColumn.Hidden = False
Sheets("sheet2").Columns("C:E").EntireColumn.Hidden = False
End Sub
They work without a glitch, even when some sheets are protected and some not. You can copy and paste this code into your VBE code window (View - Macros - View Macros - Edit). Afterwards you can duplicate and modify the code as you need to. You can also assign buttons to run the code.
I hope this helps a bit.
Kind regards
Marius Barnard
Excel Trainer
