Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot expand outline on protected sheet
Hi, I'm using Excel 2007. I have it outlined so I can expand and collapse it.
I've used it ok for months, expanding and collapsing it when protected, no problem. I'm very new to VBA. I've been adding some new code, finally got it so it works right, but now I get this message "you cannot use this command on a protected sheet" when I click on the expand/collapse symbols. I have no idea what changed. I deleted all my new code but no luck. It is not a shared spreadsheet. I have tried ActiveSheet.EnableOutlining = True, but makes no diff. What in the world changed? Thanks for any ideas you may have. Harold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot expand outline on protected sheet
This needs to be placed in a module. It will run every time the sheet is
opened Sub Auto_Open() Sheets("SheetName").Unprotect Password:="Yourpassword" With Sheets("SheetName") ..Protect Password:="Yourpassword", DrawingObjects:=False, Contents:=True, _ Scenarios:= False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, userinterfaceonly:=True ..EnableOutlining = True ..EnableAutoFilter = True End With End If End Sub Everything before the userinterface statement depends on the protection options. If you are not familiar with coding the exact option, I would use the macro recorder and unprotect and protect your sheet to get the exact statements. Note: the password part (Password:="Yourpassword",) will not record and needs to be added manually. -- If this helps, please remember to click yes. "HGood" wrote: Hi, I'm using Excel 2007. I have it outlined so I can expand and collapse it. I've used it ok for months, expanding and collapsing it when protected, no problem. I'm very new to VBA. I've been adding some new code, finally got it so it works right, but now I get this message "you cannot use this command on a protected sheet" when I click on the expand/collapse symbols. I have no idea what changed. I deleted all my new code but no luck. It is not a shared spreadsheet. I have tried ActiveSheet.EnableOutlining = True, but makes no diff. What in the world changed? Thanks for any ideas you may have. Harold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot expand outline on protected sheet
Thanks very much Paul, this did the trick.
Out of curiosity, what suddenly changed in my spreadsheet that brought up the error message that now requires this code to run, when it worked fine for the past 3 months without it? Just curious what happened? Thanks so much, Harold Paul C wrote: This needs to be placed in a module. It will run every time the sheet is opened Sub Auto_Open() Sheets("SheetName").Unprotect Password:="Yourpassword" With Sheets("SheetName") .Protect Password:="Yourpassword", DrawingObjects:=False, Contents:=True, _ Scenarios:= False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With End If End Sub Everything before the userinterface statement depends on the protection options. If you are not familiar with coding the exact option, I would use the macro recorder and unprotect and protect your sheet to get the exact statements. Note: the password part (Password:="Yourpassword",) will not record and needs to be added manually. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group and Outline on a Protected Sheet | Excel Discussion (Misc queries) | |||
Capture outline expand event? | Excel Programming | |||
Outline, expand or collapse via VBA | Excel Programming | |||
How can I allow an outline to be collapsed on a protected sheet? | Excel Worksheet Functions | |||
Why can't I show or hide rows in an outline on a protected sheet? | Excel Discussion (Misc queries) |