I have a some sheets in a workbook that have collapsible columns, but I need to have the sheet protected/locked. This is for a my company's price book that goes out to distributors, so I can't have the sheet unlocked to where they can manipulate pricing. However, I need to have collapsible columns. Is it possible to have these functional while the sheet is locked?
Yes, this is doable, but the protection parameter that allows the use of outlining tools appears to only be available in VBA. Put this macro, edited for your use, into the ThisWorkbook module of your workbook and save as a macro-enabled format, and this will work:
1) open the VBEditor (ALT-F11)
Please Login or Register to view this content.
2) Open the ThisWorkbook module in the pane on the left
3) Paste in the macro given above
4) Close the editor
5) Save the workbook as a .xlsm
http://screencast.com/t/ietm7G2M
If a user opens the workbook and does not enable macros, they won't be able to use the groups, but if they do, the groups will work.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Great instructions... I'm just about there, but I'm stuck. I'm applying this macro to multiple sheets... what am I doing wrong here?
Please Login or Register to view this content.
Last edited by JBeaucaire; 05-19-2014 at 12:27 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
The first thing you're not doing right is following the forum rules regarding CODE tags. I've added them above. Please review the forum rules and follow them for the benefit of all. Thanks.
See if this works for you:
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
My apologies. This still isn't working. Upon opening file, I get error message "Run-time error '9': Subscript out of range." I click debug, and it opens the VBEditor. The following line is highlighted in yellow:
Please Login or Register to view this content.
Subscript out of range (error 9) means the macro cannot find an object by the specific name given. One or more of the sheets listed by text strings in the array are incorrect. Perhaps a hidden space in a sheet name, or a sheet is listed there but not in existence in the workbook.
I've renamed the Sheets from "Sheet3" etc. to a product name... could that be the problem? If so, how is this resolved? Do I have to specify the new tab name? Under Microsoft Excel Objects, it dispays as "Sheet3 (product name)". Thanks for the help.
You have to decide the method you're going to use to reference a series of sheets and not others. I personally find the use of the actual sheet names, as shown in the examples in this thread, to be the most reliable.
So yes, change the "array" of text strings to list, by exact sheet names, the sheets this macro is supposed to affect.
Ok, what if I just wanted to apply it to the whole workbook? Not every worksheet has collapsible columns activated, but for some reason this code isn't working, and I know it's the "array" of text strings line. I've tried listing by code names, and also by exact sheet names, but both come back with the error.
If you truly want it to work on every sheet without exception:
Please Login or Register to view this content.
This does the trick! Thanks very much for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks