Allow users to edit range | help please

[Phanmore]

Hi,

I have a shared workbook containing multiple worksheets that have the same layout.

I am using the Allow users to edit range to give each user access to thier specific section on each sheet.

The problem I have is that I cannot see a way to protect multiple sheets at one time. Which means adding over 20 ranges on each sheet specifying the specific users that can amend thier section without using a password.

Is there any way to copy the same protection across multiple different sheets, instead of protecting them one at a time?

 

[wilcowoods]

This will protect all the worksheets at the same time:

Sub reprotect()
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Protect
Next

End Sub

But that's probably not what you want! I haven't used the allow users to edit range function but if each sheet is for a different person only, I would just unlock those cells available to each user on each sheet then protect and hide all the other sheets "VeryHidden" and have a Menu page with all the page names listed and have passwords prompted by a messagebox when they select their page name.

If all users can access all pages then I would again set macros for every user that unprotects any sheet, unlocks the appropriate cells for that person then protects the sheet - perhaps by shading areas they can't edit!

Let me know more details if you like and I can give you some code for some of the above.

Regards
Will

 

[Phanmore]

I haven't put together a code for protecting each sheet based on the application.username as there are numerous users and sheets and opening the sheet would then take some time and my colleagues don't have that much patients lol.

The allow users to edit ranges is perfect for what the spreadsheet does, i'm just looking for a quick way to apply the protection to each sheet.

I have this code from the Macro recorder, but I don't know the code for adding specific users to have access.

ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("A1:G20"), Password:="123"

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[wilcowoods]

You're right - it doesn't record User selection.

I found this snippit that may work -

ActiveSheet.Protection.AllowEditRanges(1).Users.Add "User", False

As I haven't used this function before I can't be too much help but perhaps this points the way!

 

[Phanmore]

Thanks for you help. I will give it a try now :D

 

[Phanmore]

Didn't work, gave me an application defined or object defined error.

Back to the drawring board I guess

 

[Phanmore]

I got your code working in the end, thanks again for the help. For anyone else who is interested, this is an exaple of the fianl code.

Sub alloweditrangesmacro()

Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
Sht.Protection.AllowEditRanges.Add Title:="UserName", Range:=Range("B2:D6"), Password:="123"
Sht.Protection.AllowEditRanges("UserName").Users.Add "user.name1", True

Sht.Protection.AllowEditRanges.Add Title:="UserName2", Range:=Range("B8:D13"), Password:="123"
Sht.Protection.AllowEditRanges("UserName2").Users.Add "user.name2", True

Sht.Protect
Next Sht
End Sub

 

[wilcowoods]

Great! And I've learned something new!

 

[TheBigDog]

Thank you so much. This has been VERY helpful!!