cannot expand outline on protected sheet

[HGood]

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

 

[Paul C]

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.

 

[Harold Good]

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