Trouble with an Array!!!

  • Thread starter Thread starter Keith
  • Start date Start date
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
 
Although I would expect a different error message would be generated when
you try to add any more line continued elements to your Array function call,
there is a limit of 25 maximum line continuation in any single statement.
That limit per statement is hard and fast and there is no way to avoid it.
You will have to place two or more of your Array function element on the
same line in order to not exceed the line continuation limit.

Rick
 
I don't know what you're doing wrong, since this works fine for me:

==================
Private Sub CommandButton21_Click()

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

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", "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", "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", "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", "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", "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", "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", "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", "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", "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")

For sCtr = LBound(mySheetList) To UBound(mySheetList)
Debug.Print sCtr, mySheetList(sCtr) '<---added (sctr) here!
Next sCtr

End Sub
=============
 
This is simpler than dimming an array and hardcoding all those names

Dim Sheet As Worksheet

For Each Sheet In ThisWorkbook.Worksheets
With Sheet

'your code here

End With
Next Sheet
 
And if the names are really test1, ..., test30:

Private Sub CommandButton21_Click()

'UnLock all Sheets
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

myPWD = "test"

For sCtr = 1 to 30
With Worksheets("test" & sCtr)
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
 
ps.

This line:
ActiveWindow.DisplayWorkbookTabs = True
doesn't belong in the loop.

It doesn't need to be changed 30 times.
 
ps.

This line:
ActiveWindow.DisplayWorkbookTabs = True
doesn't belong in the loop.

It doesn't need to be changed 30 times.














--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Great!! Thanks for all the info. I've gotten it to work and learned a
good bit also.
 
Back
Top