Locking/Unlocking sheets using a range.

[wilro85]

Currently I'm administrating a large spreadsheet. I've built in an
administrative page so I can quickly unlock and modify the workbook as
needed. However, I'm always interested in making my coding more
efficient.

This is an example of something I think might be fixable:

Private Sub Lockall()
PW = Range("C2").Value
Application.ScreenUpdating = False
ActiveWorkbook.Protect (PW)
Worksheets("Site 1").Protect (PW)
Worksheets("Overview").Protect (PW)
Worksheets("Site 2").Protect (PW)
Worksheets("Site 3").Protect (PW)
Worksheets("Site 4").Protect (PW)
Worksheets("Site 5").Protect (PW)
Worksheets("Site 6").Protect (PW)
Worksheets("Site 7").Protect (PW)
........(Truncated).....
Range("C4").Value = "Locked"
Application.ScreenUpdating = True
End Sub

Such programming becomes tiresome when I add new sheets into the
workbook because I have to modify the protect/unprotect hide/unhide
codes.

Is there a way that I can simplify it using a range? I'd think it
would look something like this, but all my attempts have been met with
errors.

Private Sub Lockall()
PW = Range("C2").Value
Application.ScreenUpdating = False
ActiveWorkbook.Protect (PW)
Worksheets(range("A1":"A25")).Protect (PW)
Range("C4").Value = "Locked"
Application.ScreenUpdating = True
End Sub

 

[Harald Staff]

See if this get you started on something:

Sub Lockem()
Dim PW As String
Dim oSht As Worksheet
PW = "YoDaMan"
For Each oSht In ActiveWorkbook.Worksheets
oSht.Protect (PW)
Next
End Sub

HTH. Best wishes Harald

 

[wilro85]

Works like a charm. Thanks.

 

[wilro85]

Ok wait... something isn't right. For some reason this code works wit
.protect/unprotect but doesn't seem to have the same results wit
.visible. Why not and what do I need to do?

Private Sub Unlockall()
Application.ScreenUpdating = False
Dim PW As String
Dim oSht As Worksheet
PW = Range("C2").Value
For Each oSht In ActiveWorkbook.Worksheets
oSht.Unprotect (PW)
Next
Range("C4").Value = "Unlocked"
Application.ScreenUpdating = True
End Sub

Private Sub unhide()
Application.ScreenUpdating = False
Dim PW As String
Dim oSht As Worksheet
For Each oSht In ActiveWorkbook.Worksheets
oSht.Visible = xlSheetVisible
Next
Application.ScreenUpdating = True
End Su

 

[Harald Staff]

Your code is fine, works well here. How do you experience that it doesn't
work ? Is your workbook protected ? Is the workbook in question not the
active workbook ?

Note also that an xls file needs at least one visible sheet, so hiding all
the same way will err.

Best wishes Harald

 

[wilro85]

Sure enough, I ran into the "You need to have something showing, stupid"
type error message. I haven't figured out the mechanics of how pages
are hidden yet to figure out the best way to limit the last page to
hide.

The page that I don't want hidden is called, "Summary" this is what
I've been expirmenting with.

Private Sub reset()
Call Unlockall
Application.ScreenUpdating = False
Dim PW As String
Dim oSht As Worksheet
On Error Resume Next
For Each oSht In ActiveWorkbook.Worksheets
On Worksheets("summary") GoTo ln47
oSht.Visible = False
Next
'Worksheets("summary").Visible = True
'Worksheets("summary").Select
'Worksheets("subscriber equipment").Visible = False
Call Lockall
Application.ScreenUpdating = True
MsgBox ("The form has been returned to default operation.")
End Sub

The parts that I've appostophied out was when I thought that it was
hiding them in alphabetical order, but another time I ran the macro it
ended up with another sheet unhidden. The "On Worksheets("summary")
GoTo ln47" didn't work at all, but I've not played with it that much,
yet.