Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting worksheet within an Macro
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. -- Thor ------------------------------------------------------------------------ Thor's Profile: http://www.excelforum.com/member.php...fo&userid=1049 View this thread: http://www.excelforum.com/showthread...hreadid=499160 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting worksheet within an Macro
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 -- Regards Roger Govier "Thor" wrote in message ... 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. -- Thor ------------------------------------------------------------------------ Thor's Profile: http://www.excelforum.com/member.php...fo&userid=1049 View this thread: http://www.excelforum.com/showthread...hreadid=499160 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting worksheet within an Macro
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. -- Thor ------------------------------------------------------------------------ Thor's Profile: http://www.excelforum.com/member.php...fo&userid=1049 View this thread: http://www.excelforum.com/showthread...hreadid=499160 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting worksheet | Excel Discussion (Misc queries) | |||
Protecting worksheet | Excel Worksheet Functions | |||
hiding macro codes upon protecting the worksheet | Excel Discussion (Misc queries) | |||
Macro problem with protecting worksheet........... | Excel Programming | |||
Protecting worksheet | Excel Programming |