Error!!!

K

Keith

I'm getting an error when i run this script...Run Time Error: Script
out of Range...Can anyone help? The script stops at the line i've
marked with all the stars (******************).

Private Sub CommandButton20_Click()

'Lock all Sheets

Dim mySheetList As Variant
Dim myChartList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr As Long
Dim zCtr As Long

If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
MsgBox "Contents Protected. You must have Authorization."
Exit Sub
End If

mySheetList = Array("COVER", "READY MIX", "MASONRY",
"REINFORCING", "STRUCTURAL STEEL", "LUMBER", "DRYWALL", _ "ASPHALT",
"COPPER", "OIL")


myPWD = "TEST"


For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList(sCtr))
For bCtr = 20 To 21
.OLEObjects("Commandbutton" & bCtr).Visible = False
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ActiveWindow.DisplayWorkbookTabs = False
Next bCtr
End With
Next sCtr

myChartList = Array("Ready Mix-G", "Masonry CMU-G", "Masonry SS-
G", "Masonry Mortar-G", "Reinforcing Rebar-G", _ "Reinforcing Sheets-
G", "SS-W Shape-G", "SS-Plate-G", "SS-Tube Steel-G", "SS-Metal Deck-
G", _ "SS-Bar Joist-G", "Lumber-Yellow Pine-G", "Lumber-Pressure
Treated-G", "Lumber-Fire Treated-G", _ "Lumber-CDX-G", "Lumber-Treated
CD-G", "Lumber-Dricon-G", "Lumber-OSB-G", "Drywall-Metal Stud-G", _
"Drywall-Gypsum-G", "Asphalt-G")


For zCtr = LBound(myChartList) To UBound(myChartList)
With Worksheets(myChartList(zCtr))
********************************************************************************
.Protect Password:=myPWD, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ActiveWindow.DisplayWorkbookTabs = False
End With
Next zCtr

End Sub
 
G

Gary Brown

You can't have a Worksheets(0) which is your lbound. You're trying to access
worksheets 0 thru 20 instead of worksheets 1 thru 21.
Change line from...
With Worksheets(mySheetList(sCtr))
Change line to...
With Worksheets(myChartList(zCtr + 1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top