Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with subscript out of range error.
I need your advice on this one. I've tried everything I can see but nothing
works. A vba module which controls sheets has the declarations: Option Explicit Option Base 1 Public intSheetIndex As Integer Public SheetName As Variant Public RPsheets() As Variant, PADsheets() As Variant, RPTsheets() As Variant, ANLsheets() As Variant The following sub assigns the sheets to an array: Sub Assign_Sheets() Debug.Print "enter assign" RPsheets = (Array("Summary", "Personnel", "Consultants", "Evaluation", "Equipment", _ "Travel", "Training", "Res", "Ind", "Don", _ "Loc", "Consolidated", "UserData")) ReDim Preserve RPsheets(UBound(RPsheets)) PADsheets = (Array("YR1", "YR2", "YR3", "YR4", "YR5", "CRITERIA1", "CRITERIA2", "CRITERIA3", _ "CRITERIA4", "CRITERIA5", "Comp", "CA", "CA_Sched", _ "consolidation", "xcurrencies")) ReDim Preserve PADsheets(UBound(PADsheets)) RPTsheets = (Array("FR1", "FR2", "FR3", "FR4", "FR5", "xAdmin")) ReDim Preserve RPTsheets(UBound(RPTsheets)) ANLsheets = (Array("xProject Info.", "Exp", "Pay", "CFlow", "Analysis", _ "PayRequest", "Supplement", "Xc")) ReDim Preserve ANLsheets(UBound(ANLsheets)) Debug.Print "exit assign" End Sub The following sub is where the problem is. It should protect all sheets in the arrays: Sub Protect_Sheets() Debug.Print "enter protect" Application.ScreenUpdating = False Dim i As Integer For i = 1 To UBound(RPsheets) ActiveWorkbook.Sheets(RPsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rp sheets protected" For i = LBound(PADsheets) To UBound(PADsheets) Debug.Print LBound(PADsheets) & " " & UBound(PADsheets) & " " & PADsheets(i) & " " & i ActiveWorkbook.Sheets(PADsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "pad sheets protected" For i = LBound(RPTsheets) To UBound(RPTsheets) ActiveWorkbook.Sheets(RPTsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rpt sheets protected" For i = LBound(ANLsheets) To UBound(ANLsheets) ActiveWorkbook.Sheets(ANLsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "exit protect" Application.ScreenUpdating = True End Sub Here is the debug.print log: enter assign exit assign enter protect rp sheets protected 1 15 YR1 1 1 15 YR2 2 1 15 YR3 3 1 15 YR4 4 1 15 YR5 5 1 15 CRITERIA1 6 1 15 CRITERIA2 7 1 15 CRITERIA3 8 1 15 CRITERIA4 9 1 15 CRITERIA5 10 1 15 Comp 11 1 15 CA 12 1 15 CA_Sched 13 Although the Ubound for the PAD sheets is 15 it blows out at 13. It just so happens that the RP sheets were 13. Any advice would be greatly appreciated. Thanks .... rick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with subscript out of range error.
My best guess is that you don't have a sheet named
"consolidation" Check for spelling mistakes in the sheet name or blank characters padded at the front or the back of the name. -- HTH... Jim Thomlinson "rick" wrote: I need your advice on this one. I've tried everything I can see but nothing works. A vba module which controls sheets has the declarations: Option Explicit Option Base 1 Public intSheetIndex As Integer Public SheetName As Variant Public RPsheets() As Variant, PADsheets() As Variant, RPTsheets() As Variant, ANLsheets() As Variant The following sub assigns the sheets to an array: Sub Assign_Sheets() Debug.Print "enter assign" RPsheets = (Array("Summary", "Personnel", "Consultants", "Evaluation", "Equipment", _ "Travel", "Training", "Res", "Ind", "Don", _ "Loc", "Consolidated", "UserData")) ReDim Preserve RPsheets(UBound(RPsheets)) PADsheets = (Array("YR1", "YR2", "YR3", "YR4", "YR5", "CRITERIA1", "CRITERIA2", "CRITERIA3", _ "CRITERIA4", "CRITERIA5", "Comp", "CA", "CA_Sched", _ "consolidation", "xcurrencies")) ReDim Preserve PADsheets(UBound(PADsheets)) RPTsheets = (Array("FR1", "FR2", "FR3", "FR4", "FR5", "xAdmin")) ReDim Preserve RPTsheets(UBound(RPTsheets)) ANLsheets = (Array("xProject Info.", "Exp", "Pay", "CFlow", "Analysis", _ "PayRequest", "Supplement", "Xc")) ReDim Preserve ANLsheets(UBound(ANLsheets)) Debug.Print "exit assign" End Sub The following sub is where the problem is. It should protect all sheets in the arrays: Sub Protect_Sheets() Debug.Print "enter protect" Application.ScreenUpdating = False Dim i As Integer For i = 1 To UBound(RPsheets) ActiveWorkbook.Sheets(RPsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rp sheets protected" For i = LBound(PADsheets) To UBound(PADsheets) Debug.Print LBound(PADsheets) & " " & UBound(PADsheets) & " " & PADsheets(i) & " " & i ActiveWorkbook.Sheets(PADsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "pad sheets protected" For i = LBound(RPTsheets) To UBound(RPTsheets) ActiveWorkbook.Sheets(RPTsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rpt sheets protected" For i = LBound(ANLsheets) To UBound(ANLsheets) ActiveWorkbook.Sheets(ANLsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "exit protect" Application.ScreenUpdating = True End Sub Here is the debug.print log: enter assign exit assign enter protect rp sheets protected 1 15 YR1 1 1 15 YR2 2 1 15 YR3 3 1 15 YR4 4 1 15 YR5 5 1 15 CRITERIA1 6 1 15 CRITERIA2 7 1 15 CRITERIA3 8 1 15 CRITERIA4 9 1 15 CRITERIA5 10 1 15 Comp 11 1 15 CA 12 1 15 CA_Sched 13 Although the Ubound for the PAD sheets is 15 it blows out at 13. It just so happens that the RP sheets were 13. Any advice would be greatly appreciated. Thanks .... rick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with subscript out of range error.
If the activesheet doesn't have a worksheet by that name, your code will fail.
I'd check the existence first. And I'd also make sure that those variables are defined before I tried to use them. Depending on what you've done while debugging, the variables may be empty. I'd use something like: Option Explicit Option Base 1 Public RPsheets() As Variant Dim PADsheets() As Variant Dim RPTsheets() As Variant Dim ANLsheets() As Variant Dim VarsAreAssigned As Boolean Sub Assign_Sheets() Debug.Print "enter assign" RPsheets = Array("Summary", "Personnel", "Consultants", _ "Evaluation", "Equipment", _ "Travel", "Training", "Res", "Ind", "Don", _ "Loc", "Consolidated", "UserData") PADsheets = Array("YR1", "YR2", "YR3", "YR4", "YR5", _ "CRITERIA1", "CRITERIA2", "CRITERIA3", _ "CRITERIA4", "CRITERIA5", "Comp", "CA", "CA_Sched", _ "consolidation", "xcurrencies") RPTsheets = Array("FR1", "FR2", "FR3", "FR4", "FR5", "xAdmin") ANLsheets = Array("xProject Info.", "Exp", "Pay", "CFlow", "Analysis", _ "PayRequest", "Supplement", "Xc") VarsAreAssigned = True Debug.Print "exit assign" End Sub Sub Protect_Sheets() Debug.Print "enter protect" Dim i As Long Dim TestWks As Worksheet Application.ScreenUpdating = False If VarsAreAssigned = False Then Call Assign_Sheets End If For i = LBound(RPsheets) To UBound(RPsheets) Set TestWks = Nothing On Error Resume Next Set TestWks = ActiveWorkbook.Worksheets(RPsheets(i)) On Error GoTo 0 If TestWks Is Nothing Then MsgBox ActiveWorkbook.Name & " doesn't have a sheet named: " _ & RPsheets(i) Else With TestWks .Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True, AllowInsertingRows:=False .EnableSelection = xlUnlockedCells End With End If Next i Debug.Print "rp sheets protected" For i = LBound(PADsheets) To UBound(PADsheets) Set TestWks = Nothing On Error Resume Next Set TestWks = ActiveWorkbook.Worksheets(PADsheets(i)) On Error GoTo 0 If TestWks Is Nothing Then MsgBox ActiveWorkbook.Name & " doesn't have a sheet named: " _ & PADsheets(i) Else With TestWks .Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True, AllowInsertingRows:=False .EnableSelection = xlUnlockedCells End With End If Next i Debug.Print "pad sheets protected" For i = LBound(RPTsheets) To UBound(RPTsheets) Set TestWks = Nothing On Error Resume Next Set TestWks = ActiveWorkbook.Worksheets(RPTsheets(i)) On Error GoTo 0 If TestWks Is Nothing Then MsgBox ActiveWorkbook.Name & " doesn't have a sheet named: " _ & RPTsheets(i) Else With TestWks .Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True, AllowInsertingRows:=False .EnableSelection = xlUnlockedCells End With End If Next i Debug.Print "rpt sheets protected" For i = LBound(ANLsheets) To UBound(ANLsheets) Set TestWks = Nothing On Error Resume Next Set TestWks = ActiveWorkbook.Worksheets(ANLsheets(i)) On Error GoTo 0 If TestWks Is Nothing Then MsgBox ActiveWorkbook.Name & " doesn't have a sheet named: " _ & ANLsheets(i) Else With TestWks .Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True, AllowInsertingRows:=False .EnableSelection = xlUnlockedCells End With End If Next i Debug.Print "anl sheets protected" Application.ScreenUpdating = True Debug.Print "exit protect" End Sub I used msgbox's, but you could use debug.print's if you don't want to inform the user. rick wrote: I need your advice on this one. I've tried everything I can see but nothing works. A vba module which controls sheets has the declarations: Option Explicit Option Base 1 Public intSheetIndex As Integer Public SheetName As Variant Public RPsheets() As Variant, PADsheets() As Variant, RPTsheets() As Variant, ANLsheets() As Variant The following sub assigns the sheets to an array: Sub Assign_Sheets() Debug.Print "enter assign" RPsheets = (Array("Summary", "Personnel", "Consultants", "Evaluation", "Equipment", _ "Travel", "Training", "Res", "Ind", "Don", _ "Loc", "Consolidated", "UserData")) ReDim Preserve RPsheets(UBound(RPsheets)) PADsheets = (Array("YR1", "YR2", "YR3", "YR4", "YR5", "CRITERIA1", "CRITERIA2", "CRITERIA3", _ "CRITERIA4", "CRITERIA5", "Comp", "CA", "CA_Sched", _ "consolidation", "xcurrencies")) ReDim Preserve PADsheets(UBound(PADsheets)) RPTsheets = (Array("FR1", "FR2", "FR3", "FR4", "FR5", "xAdmin")) ReDim Preserve RPTsheets(UBound(RPTsheets)) ANLsheets = (Array("xProject Info.", "Exp", "Pay", "CFlow", "Analysis", _ "PayRequest", "Supplement", "Xc")) ReDim Preserve ANLsheets(UBound(ANLsheets)) Debug.Print "exit assign" End Sub The following sub is where the problem is. It should protect all sheets in the arrays: Sub Protect_Sheets() Debug.Print "enter protect" Application.ScreenUpdating = False Dim i As Integer For i = 1 To UBound(RPsheets) ActiveWorkbook.Sheets(RPsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rp sheets protected" For i = LBound(PADsheets) To UBound(PADsheets) Debug.Print LBound(PADsheets) & " " & UBound(PADsheets) & " " & PADsheets(i) & " " & i ActiveWorkbook.Sheets(PADsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "pad sheets protected" For i = LBound(RPTsheets) To UBound(RPTsheets) ActiveWorkbook.Sheets(RPTsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "rpt sheets protected" For i = LBound(ANLsheets) To UBound(ANLsheets) ActiveWorkbook.Sheets(ANLsheets(i)).Activate ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowInsertingRows:=False ActiveSheet.EnableSelection = xlUnlockedCells Next i Debug.Print "exit protect" Application.ScreenUpdating = True End Sub Here is the debug.print log: enter assign exit assign enter protect rp sheets protected 1 15 YR1 1 1 15 YR2 2 1 15 YR3 3 1 15 YR4 4 1 15 YR5 5 1 15 CRITERIA1 6 1 15 CRITERIA2 7 1 15 CRITERIA3 8 1 15 CRITERIA4 9 1 15 CRITERIA5 10 1 15 Comp 11 1 15 CA 12 1 15 CA_Sched 13 Although the Ubound for the PAD sheets is 15 it blows out at 13. It just so happens that the RP sheets were 13. Any advice would be greatly appreciated. Thanks ... rick -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with subscript out of range error.
Thank you both very much for your speedy replies and your insight. By using
your code, Dave, I saw I had a sheet renamed. ....rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming | |||
Subscript out of range error | Excel Programming |