R
rick
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
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