Grouping data and protecting worksheets

[bj]

Hi there,

I have grouped a number of rows in my excel sheet so I can collapse the rows
into 1 row.

I then want to protect the worksheet from end users however I also want them
to be able to expand and collapse the group with the small grey plus/minus
box that appears next to the grouped column rows.

Currently when i put protection on the worksheet it protects the sheet but
it also restricts users from expanding/collapsing the grouped rows. Is there
a way around this?

Thanks in advance.

 

[Daveo]

Hi there,

Click Tools - Protection - Allow Users To Edit Ranges....

Hit New and enter the range of the cells that are hidden/grouped e.g.
$A:$G.

Press OK and that should allow you to do it.

Note: I tried it on Excel 2002 and am not sure if earlier versions have
this feature.

Thanks - David

 

[bj]

Hi David,

Unfortunately as you predicted it looks like Excel 2000 dosent have this
feature

 

[Daveo]

You could work around by adding a command button in row 1 of the next
column along and add the following code:

Private Sub cmdTest_Click()

ActiveSheet.Unprotect Password:="yourpassword"

If Range("A:E").EntireColumn.Hidden = True Then

Range("A:E").EntireColumn.Hidden = False

Else

If Range("A:E").EntireColumn.Hidden = False Then

Range("A:E").EntireColumn.Hidden = True

Else: End If

End If

ActiveSheet.Protect Password:="yourpassword"

End Sub

You would also have to password protect the VBA project or anyone could
see the sheet password by opening the VBA editor.

Hope this helps - David

 

[Daveo]

Whoops - just noticed you mean rows!!:

Change the ranges to rows instead of column and use EntireRow.Hidden
instead.

 

[Dave Peterson]

If you already have the outlining applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm