Protecting worksheet within an Macro

[Thor]

Hello. Boy, do I need help....

I had to unprotect and protect a worksheet within a macro to get some
code to run. Problem: The protection variables that I originally
assigned when I manually protected the sheet (and before the macro was
run for the first time), such as allow the user to format unlocked
cells, columns, and rows is modified. Is there a way to keep these
protection variables in tact with the macro code?? I'm running excel
2003, but some users will be running 2000.

Worksheets("DOLLARS").Unprotect Password:="HAPPY"
Range("U9:AF9").Select
Selection.EntireColumn.Hidden = True
Range("AG10:AN10").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("B13").Select
Worksheets("DOLLARS").Protect Password:="HAPPY"
End Sub

I appreciate any input.

 

[Roger Govier]

Hi Thor

Try something like

Worksheets("DOLLARS").Unprotect Password:="HAPPY"
Range("U9:AF9").EntireColumn.Hidden = True
Range("AG10:AN10").EntireColumn.Hidden = False
Range("B13").Select
ActiveSheet.Protect Password="HAPPY", DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True,
AllowFiltering:=True, _
AllowUsingPivotTables:=True

 

[Thor]

Roger:
Thx for the tip. This is good as you wrote for 2003, however it doesn't
work in 2000 and a runtime 1004 error is created. I removed the
"allow---" formatting code only, because that appears to be applicable
to 2003 only, and ran it again in 2000 with the same 1004 error. This
might be a case that there is no way to run this across both
versions...

Thank you.
Thor.