access to worksheet protected by different passwords

Here is a simple way to have different passwords for different ranges, and then also have a master password. It uses the in-built "Allows Users to Edit Ranges" feature.

Please Login or Register  to view this content.

Wow, thanks alot, next question, how do i put that into VBA - Module? Sheet1? what needs to go at the start and end of the code?
Thanks muchly!
galvinpaddy

You can set this manually, if you want.

1: Go to the Review tab
2: Select "Allow Users to Edit Ranges"
3: Click "New"
4: Set Name, Range, and Password as desired.
5: Repeat Password to verify
6: Repeat steps 3 - 5 as needed.
7: Click "Protect Worksheet"
8: Supply password (Note, this password is a "master" password and will unlock all cells in the sheet)
9: Repeat password to verify.

This will lock the sheet and allow different ranges to be accessed with different passwords.

To remove the lock, simply go to the Review tab and click "Unprotect Sheet", then supply the master password.

To edit the ranges/passwords, unlock the sheet as directly above, then click "Allow User to Edit Ranges" and modify to suit.

To do all the above programatically, paste the following code into a regular module and edit to suit:

Please Login or Register  to view this content.

Fantastic fella,
Thanks so much!!! Well deserved Rep added :D

Nice one thanks fella, that code will ehlp for another sheet (shared between areas)

Stuff of legends mate, cheers!
galvinpaddy

Hi Fella,
Apologies for more questions, the below code works great, however, i would like to set the sheet as 'Share Workbook & Allow changes by more than one user at the same time'.
I have 2 cmd buttons on the sheet, one that will basically activate the below code (so re-locking all unlocked cells, with 'Activeworkbook.SAVE added) and the second is a 'UserAns' box, that asks for the MasterPassword (Lianne) to enable the sheet to be modified fully.
When i share the workbook, everything works fine, until i click on the cmd button to activate the code, then i receive an error that simply says "400" with the big red X next to it.
Any ideas??

Sub LockCellsLive()

ActiveWorkbook.SAVE
Dim ProtectionArray(1, 3)
Dim aer As AllowEditRange
Dim x As Integer

ActiveSheet.Unprotect Password:="Lianne" 'Supply current password. If this macro has already been run, then enter the Master Password below

ProtectionArray(1, 1) = "Range1" 'Name
ProtectionArray(1, 2) = "(J:J,K:K,O:O,P:P,Q:Q,R:R)" 'Address
ProtectionArray(1, 3) = "Test" 'Password


For Each aer In ActiveSheet.Protection.AllowEditRanges
aer.Delete
Next aer

For x = 1 To UBound(ProtectionArray)
With ActiveSheet.Protection
.AllowEditRanges.Add Title:=ProtectionArray(x, 1), Range:=Range(ProtectionArray(x, 2)), Password:=ProtectionArray(x, 3)
End With
Next x
ActiveSheet.Protect Password:="Lianne", DrawingObjects:=True, Contents:=True, Scenarios:=True 'Password is the Master Password to unlock all cells.

End Sub

You cannot protect or unprotect a sheet while the workbook is shared. The sharing would need to be removed for the brief time it takes to modify the protection.

You can either do this manually or through code. Manually will allow you to check if any users currently have the workbook open.