Macro to define "Edit Ranges" password on multiple worksheets in workbook


New Member
Jan 8, 2016
Hello! I have a workbook with multiple nearly-identical worksheets; each one is a timesheet for two-week periods. I need the same range of cells on every worksheet to be defined under "Allow Edit Ranges" and set with a password in order to edit those ranges. I can use the same password for every sheet - so again, it's the same range of cells in every sheet and same password. I'm hoping for a macro that would allow me to set up the "Edit Ranges" on all the worksheets at once, rather than having to do each one separately. I have a very similar macro that I use to protect and unprotect all the worksheets with a single macro - is the same possible for the "Edit Ranges" function?

For reference - the range I want to define as edit-able with a password is:

And the password can be:

Any help would be much appreciated!


New Member
May 12, 2014
Sub EditRange()
Dim Sh As Worksheet
Dim Rng As Range
Dim i As Integer
Set Sh = Application.ActiveSheet
On Error Resume Next
' Unprotect worksheet.
Set Rng = Sh.Range("$C$3:$F$16", "$M$3:$N$16")
' Delete all current protection ranges

'MsgBox ws.Protection.AllowEditRanges.Count

For i = 1 To Sh.Protection.AllowEditRanges.Count


' Establish a range that can allow edits
' on the protected worksheet.
Sh.Protection.AllowEditRanges.Add Title:="Classified", Range:=Rng, Password:="6007"
End Sub

Forum statistics

Latest member
Winfield Ullrich I

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...