How to allow 'Group/Ungroup' on a Protected and Locked Worksheet

Question

How to allow 'Group/Ungroup' on a Protected and Locked Worksheet

Anonymous

Right, this may be structures and security so lets try...

I have a pretty big Excel Progress Claim workbook, that is 'organised' with worksheets (tabs) like below:

Actual Claim summary, working over 968 lines for the Project work items
Several tabs with Global/General lookup information that goes to the Area sheets (as below)
Area worksheets, for the areas Progress Claim summary. Anywhere from 150 lines (summing to individual claim items on the main Claim sheet) to 1000 lines. 42 cells wide with 7 that get updated by the area. Others are sums/subtotals/formulaes using lookups etc
One check sheet that ensure figures in one place, add to same in another

Now as these sheets are too wide and long for easy use, there are some 'Grouping' areas setup on both a Row and Column basis. All cells that the users are meant to update are unlocked, all others locked (to stop interlinked formulaes being corrupted).

When I lock the sheet though, uses cannot use the grouping? but if you allow 'structure changes' they can add/delete lines? How can I lock the sheets, forcing them to only update their values, but still allow them to use the group/ungroup as setup?

TK = Toe Knee (....Tony? get it?)

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

5 answers

Answer 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

Answer 2

Anonymous

Try With Worksheets(Array("sheet1","sheet2","Sheet3"))

0 comments

Answer 3

Ok... I know I'm 3 year's after this post was active.. but have a question about this process. I have been able to get the macro to work with 1 worksheet, but I have a couple of worksheets that I want to be able to protect but still be able to expand/collaps the groups... I tried just adding the other worksheets in the format...

Sub auto_open()

With Worksheets("sheet1","sheet2","Sheet3")

But I get a compile error: "Wrong number of arguments or invalid property assignment"

What am I missing? Passwords are the same on all worksheets...?

Answer 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

Answer 5

Right, this may be structures and security so lets try...

I have a pretty big Excel Progress Claim workbook, that is 'organised' with worksheets (tabs) like below:

Actual Claim summary, working over 968 lines for the Project work items

Several tabs with Global/General lookup information that goes to the Area sheets (as below)

Area worksheets, for the areas Progress Claim summary. Anywhere from 150 lines (summing to individual claim items on the main Claim sheet) to 1000 lines. 42 cells wide with 7 that get updated by the area. Others are sums/subtotals/formulaes using lookups etc

One check sheet that ensure figures in one place, add to same in another

Now as these sheets are too wide and long for easy use, there are some 'Grouping' areas setup on both a Row and Column basis. All cells that the users are meant to update are unlocked, all others locked (to stop interlinked formulaes being corrupted).

When I lock the sheet though, uses cannot use the grouping? but if you allow 'structure changes' they can add/delete lines? How can I lock the sheets, forcing them to only update their values, but still allow them to use the group/ungroup as setup?

TK = Toe Knee (....Tony? get it?)

A walk around the problem. Try using: Custom Views. http://office.microsoft.com/en-us/excel-help/create-or-delete-custom-views-HP005199633.aspx Custom views work even with protected sheet.

http://www.maurogsc.eu/