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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PK HariConnect With a Mentor APAC 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
 
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
All Courses

From novice to tech pro — start learning today.