cannot expand outline on protected sheet


H

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
 
Ad

Advertisements

P

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.
 
Ad

Advertisements

H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top