Expanding/Collapsing Grouped Columns/Rows When a Sheet is Protected

Anonymous
2010-08-18T14:07:51+00:00

I want to protect the locked cells in any given spreadsheet so that the user cannot override formula cells.  I have also grouped columns that contain historical information.  When I protect the sheet, I cannot expand or collapse the grouped columns.  I have tried checking boxes when in the protection dialogue box such as "Format columns", with no success.  I use Excel 2007, but it is saved as a 2003 file because some users are still with that version.

Is it possible to expand or collapse grouped columns when a sheet is protected?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

41 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2015-09-24T16:40:02+00:00

    sounds like you are putting it in the wrong place.

    do the Alt+F11.   In the project explorer on the left, find your workbook/project.  You should see ThisWorkbook under modules.  Right click on that and select view code.

    Paste you code in that module.

    It is the module used to house workbook level events.  the Workbook_Open event is a workbook level event.

    When the module first appears, it may already be populated with a workbook_Open event skeleton.  You can delete that and then paste in your code.

    The option explict  must appear at the top of the module.

    now delete the same code in the module you inserted.   Save the workbook.

    Code in the workbook_open event should run everytime the workbook is opened and code is not disabled.

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-09-24T16:58:24+00:00

    now delete the same code in the module you inserted.   

    Tom-

    Thanks so much for the quick reply! I didn't get that last bit, though. If I delete the code that I just inserted before saving it, wouldn't that leave me at square one, with no code at all?

    I'm assuming I just misunderstood you, but I still can't figure out what was meant.

    Thanks

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2015-09-24T18:50:59+00:00

    It works! Except for one thing. When opening the file for the first time, it has a warning to "enable macros". 

    Is there a way for the file to use the code without manually enabling macros every time it is opened? I'm sharing this file with a team of people that will probably not understand what that macro is for, and I could see them disabling the macros after opening the file.

    Was this answer helpful?

    0 comments