How to show or hide outlined data option in a protected worksheet?


G

Guest

I've created a worksheet for others to use with grouped and outlined sections
so the user can view the information either expanded with detail or collapsed
to hide the detail. This worksheet also contains certain cells that are
protected from the user making changes to them. When the protection is
activated and I try to use the hide or show detail option I get this error
message. "You cannot use this command on a protected sheet" Is there a way
to protect certain cells and allow the user to show and hide detail depending
upon their preference.

I'm using Excel 2003, MS Windows XP Pro.

Thanks!
 
Ad

Advertisements

J

Jim Rech

The only way to do what you want is to set the sheet's EnableOutlining
property to True and to protect the sheet via macro with the
UserInterfaceOnly parameter set to true. This is an example of the code to
do that:

Sheet1.EnableOutlining = True
Sheet1.Protect "password", True, True, True, True

This code has to be run whenever the workbook is opened because
UserInterfaceOnly is not a setting that is saved with the workbook. So it
has to be run by the Workbook_Open event sub or Sub Auto_Open.
 
Ad

Advertisements

G

Guest

That's exactly what I needed!

I had to get some help on the macro part (thus my delay in my response) and
now it's working great. You taught me and macro helper something. Tx!
 

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