Trouble with an Array!!!
 

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

Trouble with an Array!!!
 

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


"Keith" wrote in message
...
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

Trouble with an Array!!!
 

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
=============


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Keith" wrote:

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

Trouble with an Array!!!
 

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


"Keith" wrote:

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

Trouble with an Array!!!
 

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

Keith wrote:

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

--

Dave Peterson

Trouble with an Array!!!
 

ps.

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

It doesn't need to be changed 30 times.

Dave Peterson wrote:

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

Keith wrote:

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

--

Dave Peterson

--

Dave Peterson

Trouble with an Array!!!
 

On Jul 21, 4:37*pm, Dave Peterson wrote:
ps.

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

It doesn't need to be changed 30 times.





Dave Peterson wrote:

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

Keith wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

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