Excel 2007: How to allow expanding/collapsing of grouped columns/rows when the sheet is protected?

Hi, I group a few columns together in a worksheet.  I can expand the columns by clicking on "+" button and collapse them again with the "-" button.  However, this is not allow when I protect the sheet.  I want to be able to expand and collapse while protecting some cells in the worksheet.
JCJGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PK HariAPAC Financial ControllerCommented:
Hi

It is not possible to allow grouping / ungrouping of cells when the sheet is protected. You can achieve this only by leaving the sheet unprotected or by handling the whole thing through VBA Code.

Cheers
P.K.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JCJGAuthor Commented:
Thanks.
0
hlinapCommented:
Hello,

fortunatelly that's not really true. It is possible, see:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21680434.html?sfQueryTermInfo=1+excel+expand+how+protect+when

It uses a VBA trick. I've tested it on Excel 2000 and it works fine. To make it work you may need close and re-open the workbook afterwards.

Perhaps, in some newer version of Excel, this would be an checkbox option when you protecting a worksheet manually from Excel's menu.

Cheers
Hlina
0
hlinapCommented:
Hello,

pkhari: oh, you wrote that it is possible through VBA code. I didn't notice this. My apologies!

So, please, consider my previous comment as just a link to the VBA code.

Regards,
Hlina
0
cemathwigCommented:
Regarding Excel 2007: How to allow expanding/collapsing of grouped columns/rows when the sheet is protected?

A question/solution (highly rated) was posted:
Hi, I group a few columns together in a worksheet.  I can expand the columns by clicking on "+" button and collapse them again with the "-" button.  However, this is not allow when I protect the sheet.  I want to be able to expand and collapse while protecting some cells in the worksheet.

I want to do similar, but with rows. The VBA solution (and others similar that I have tried) do not work. No amount of VBA appears to work, even with macros enabled. I'm not sure what I'm doing wrong. Any other solutions??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.