Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson

When I protect the sheet I check "Format Columns" & "Format Rows" that will
allow users to change the height of rows and width of columns. With auto_open
it looks like it overrides these choices. But the Filtering works. Is there a
way I can get all of them to work?

Thanks

Ram

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson

Record a macro when you change the protection on the worksheet and change the
settings you want.

Then include them in .protect line

..Protect Password:="hi", userinterfaceonly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True





Ram B wrote:

When I protect the sheet I check "Format Columns" & "Format Rows" that will
allow users to change the height of rows and width of columns. With auto_open
it looks like it overrides these choices. But the Filtering works. Is there a
way I can get all of them to work?

Thanks

Ram

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson


--

Dave Peterson

If I have 10 tabs and want to use this code on all of them, how do I write
all of the sheets into it? (i.e. i want the line to read With
Worksheets("00,01,02,etc")--but I get an error every way I try to put in
multiple sheets). Any ideas?

"Dave Peterson" wrote:

Record a macro when you change the protection on the worksheet and change the
settings you want.

Then include them in .protect line

..Protect Password:="hi", userinterfaceonly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True





Ram B wrote:

When I protect the sheet I check "Format Columns" & "Format Rows" that will
allow users to change the height of rows and width of columns. With auto_open
it looks like it overrides these choices. But the Filtering works. Is there a
way I can get all of them to work?

Thanks

Ram

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson


--

Dave Peterson

Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
next wks
End Sub

If you wanted to avoid any sheets (based on a name):


Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
With wks
select case lcase(.name)
case is = lcase("sheet1"),lcase("sheet99")
'do nothing, skip it
case else
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
end select
End With
next wks
End Sub


Michelle Thompson wrote:

If I have 10 tabs and want to use this code on all of them, how do I write
all of the sheets into it? (i.e. i want the line to read With
Worksheets("00,01,02,etc")--but I get an error every way I try to put in
multiple sheets). Any ideas?

"Dave Peterson" wrote:

Record a macro when you change the protection on the worksheet and change the
settings you want.

Then include them in .protect line

..Protect Password:="hi", userinterfaceonly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True





Ram B wrote:

When I protect the sheet I check "Format Columns" & "Format Rows" that will
allow users to change the height of rows and width of columns. With auto_open
it looks like it overrides these choices. But the Filtering works. Is there a
way I can get all of them to work?

Thanks

Ram

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Michelle

Go back to your other post and see one reply.

If you remember where it is.

Best to post your own questions rather than piggy-backing on other people's
postings.


Gord Dibben MS Excel MVP

On Fri, 4 Dec 2009 12:31:01 -0800, Michelle Thompson
wrote:

If I have 10 tabs and want to use this code on all of them, how do I write
all of the sheets into it? (i.e. i want the line to read With
Worksheets("00,01,02,etc")--but I get an error every way I try to put in
multiple sheets). Any ideas?

"Dave Peterson" wrote:

Record a macro when you change the protection on the worksheet and change the
settings you want.

Then include them in .protect line

..Protect Password:="hi", userinterfaceonly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True





Ram B wrote:

When I protect the sheet I check "Format Columns" & "Format Rows" that will
allow users to change the height of rows and width of columns. With auto_open
it looks like it overrides these choices. But the Filtering works. Is there a
way I can get all of them to work?

Thanks

Ram

"Dave Peterson" wrote:

If you already have the outline/subtotals/autofilter 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
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ram B wrote:

Does anyone know how to protect a worksheet and still have access to the
"Group/Ungroup" functionality on the left ? Thanks!

--

Dave Peterson


--

Dave Peterson