Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array trouble | Excel Programming | |||
Trouble Passing An Array | Excel Programming | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
Trouble using the { } in array formula (for multiple criteria) | Excel Programming | |||
Trouble to sort an array in Excel using VBA | Excel Programming |