K
Keith
Does anyone know the maximum items you can have in an array? I have
25. When i enter any more i get "Compile error: Expected: expression".
Or does anyone know a way around this. I'm trying to write code that
will lock and unlock all sheets in a workbook. I have approx 10 more
sheets to enter. See code below...
"Private Sub CommandButton21_Click()
'UnLock all Sheets
Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr 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("test1", _
"test2", _
"test3", _
"test4", _
"test5", _
"test6", _
"test7", _
"test8", _
"test9", _
"test10", _
"test11", _
"test12", _
"test13", _
"test14", _
"test15", _
"test16", _
"test17", _
"test18", _
"test19", _
"test20", _
"test21", _
"test22", _
"test23", _
"test24", _
"test25")
myPWD = "test"
For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList(sCtr)) '<---added (sctr) here!
For bCtr = 20 To 21
.OLEObjects("Commandbutton" & bCtr).Visible = True
.Protect Password:=myPWD, DrawingObjects:=False, _
Contents:=False, Scenarios:=False
ActiveWindow.DisplayWorkbookTabs = True
Next bCtr
End With
Next sCtr
End Sub
25. When i enter any more i get "Compile error: Expected: expression".
Or does anyone know a way around this. I'm trying to write code that
will lock and unlock all sheets in a workbook. I have approx 10 more
sheets to enter. See code below...
"Private Sub CommandButton21_Click()
'UnLock all Sheets
Dim mySheetList As Variant
Dim sCtr As Long
Dim myPWD As String
Dim bCtr 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("test1", _
"test2", _
"test3", _
"test4", _
"test5", _
"test6", _
"test7", _
"test8", _
"test9", _
"test10", _
"test11", _
"test12", _
"test13", _
"test14", _
"test15", _
"test16", _
"test17", _
"test18", _
"test19", _
"test20", _
"test21", _
"test22", _
"test23", _
"test24", _
"test25")
myPWD = "test"
For sCtr = LBound(mySheetList) To UBound(mySheetList)
With Worksheets(mySheetList(sCtr)) '<---added (sctr) here!
For bCtr = 20 To 21
.OLEObjects("Commandbutton" & bCtr).Visible = True
.Protect Password:=myPWD, DrawingObjects:=False, _
Contents:=False, Scenarios:=False
ActiveWindow.DisplayWorkbookTabs = True
Next bCtr
End With
Next sCtr
End Sub