Grouped columns on protected sheet

[sarahstewart]

Hi All. Experiencing a strange issue with one of my workbooks and I’m hoping someone may be able to shed some light.

I have a sheet which has grouped columns and the sheet is protected. Under sheet protection, the option for “format columns” is checked.

In testing, most users are able to press the (+) and (-) buttons to expand and collapse the grouped columns with no issues.

However a couple of testers (using exactly the same machines, on the same network with the same version of excel) cannot expand or collapse the grouped columns and instead receive a pop up message saying “the command cannot be used on a protected sheet.”

I have been unable to identify why this is working for some users but not all. I would prefer a non-VBA method of allowing all users to expand/collapse grouped columns but need to know that it will work for all users.

Any ideas anyone?!

Thank you,

Sarah

[#2]

Hi sarahstewart, did you manage to solve this issue? I have the same problems, got a sheet with grouping for which I would like to grey out AZ and number sorting, but leave filtering possible. Once I set these last two options with protect sheet, grouping is also disable and I cannot use the + or - signs.

Thank you for any input.

[#3]

Hello,

Your problem sounds a little different than mine- my issue was that the protect options were working fine for some people using the spreadsheet, but not for others.

In the end I had to use a macro that allows protected sheets to be grouped and in grouped. This way, all users were able to use the grouping function with no issues. It was just annoying as it shouldn’t have been required- the protect options should have worked for all users. Never got to the bottom of why it didn’t.

Here’s a link to the VBA code for the whole workbook which I used:

https://www.thespreadsheetguru.com/the-code-vault/groups-expand-collapse-sheet-password-protection

Good luck!

[#4]

@sarahstewart 

With your permission, please read the link below.

In order to get a quick and correct proposal for a solution, it is always advantageous to know which version of Excel it is, which operating system it is and where is this Excel file located?

... OneDrive, Sharepoint, hard drive, etc.

So you and everyone who wants to help will be helped.

Welcome to your Excel discussion space!

Thank you for your patience and understanding.

Nikolino

I know I don't know anything (Socrates)