Enable Outlining with a protected worksheet.

[herkiefan]

Hi, this is a continuation of thread68-1250213: Use Group and Outline in Excel with Protection On, where the VBA code was explained to allow the grouping/ungrouping of outlined columns/cells within a protected worksheet.

I have followed (uncorrectly, I guess) the directions given and I am not able to get the macros to work.

I am using Excel 2003 SP2.
This is what I would like to do:

Protect worksheet "Lookup Tool".
Allow users to change the values in Cells A10 and A34.
Allow users to open and close the +/- grouping buttons.
Allow users to format columns to remove #####'s when a result value is too large.

I entered the following code in the Sheet1 (Lookup Tool) module within Visual Basic Editor:

Option Explicit

Sub auto_open()
With Worksheets("Sheet1")
.Protect Password:="lookup", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Sub Protect_Sheet()
ActiveSheet.EnableOutlining = True
ActiveSheet.Protect contents:=True, userinterfaceonly:=True
End Sub

I have locked the entire worksheet sans A10 and A34.
The protection boxes that I checked are:
Select locked cells
Select unlocked cells
Format cells
Format columns
Format rows
Insert columns

When I open the file I am prompted to Enable macros, which I do. I can change cells A10 and A34. However, when I try to Close the open groups columns BCD and FGH by clicking on the "-" button, Excel screams at me with "You cannot use this command...."

What am I doing wrong?

Thanks,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[combo]

Just to clarify:
1. userinterfaceonly parameter will not help you. It allows (when set to true) to modify protected sheet by code, but still lock it for the user.
2. The Autpen prosedure should be in regular module. The newer version Workbook_Open works in ThisWorkbook module, the template of this event procedure can be generated by choosing Workbook object from the left dropdown and Open event from the right one.
3. Since excel xp you can precise what is to be protected on protected worksheet. This can be set manually or by code. If manually, just deselect options while protecting the worksheet.

Concluding, if you need to change worksheet by code, set UserInterfaceOnly parameter to True, otherwise forget it. Depending on macro you use (Autpen, Workbook_Open), properly locate it.
If you do not plan to use code, just select/deselect protection options and protect the worksheet.

combo