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

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.

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

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...?

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