Caitlin535
New Member
- Joined
- Jan 8, 2016
- Messages
- 21
Hello -
I have an Excel system that i use to track timekeeping with my employees. There is a row for each day, and in one cell they select "Arrived" from a drop down menu, enter a pin number, and then it automatically timestamps the date/time in another column. I have a macro that i use to set the pin number for the protected range on each sheet (see below). It used to work such that it would set the pin that I specified for *all* the worksheets in the file, but now it only sets it for the active worksheet. I feel like I'm missing something fairly simple - how can I get it to operate for *all* the worksheets in the file?
I have an Excel system that i use to track timekeeping with my employees. There is a row for each day, and in one cell they select "Arrived" from a drop down menu, enter a pin number, and then it automatically timestamps the date/time in another column. I have a macro that i use to set the pin number for the protected range on each sheet (see below). It used to work such that it would set the pin that I specified for *all* the worksheets in the file, but now it only sets it for the active worksheet. I feel like I'm missing something fairly simple - how can I get it to operate for *all* the worksheets in the file?
VBA Code:
Sub EditRange()
Dim Sh As Worksheet
Dim Rng As Range
Dim i As Integer
Set Sh = Application.ActiveSheet
On Error Resume Next
' Unprotect worksheet.
Sh.Unprotect
Set Rng = Sh.Range("$C$5:$F$18, $M$5:$N$18, $P$5:$Q$18")
' Delete all current protection ranges
'MsgBox ws.Protection.AllowEditRanges.Count
For i = 1 To Sh.Protection.AllowEditRanges.Count
Sh.Protection.AllowEditRanges(i).Delete
Next
' Establish a range that can allow edits
' on the protected worksheet.
Sh.Protection.AllowEditRanges.Add Title:="PINRange", Range:=Rng, Password:="a5678"
End Sub