Protection - Allow Group/Ungroup but lock / unlock some cells
 

Hi Everyone

I have a spreadsheet which allows people to enter some figures in some cells.
Those cells then add together to creat bew totals.

I want to allow them to see all the individual lines or allow them to use
the group +or - (the rows have already been grouped) to change the views.

But, if I lock some cells in those rows, the Group + or - no longer works.

Is there anyway around? I've tried "Allow Format rows" as a protection
option but it didn't work. I've tried to select the whole worksheet, unlocked
it, then locked smalls parts, that didn't work either.

If there is a solution, I'd love to hear from you!

Protection - Allow Group/Ungroup but lock / unlock some cells
 

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

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

LinLin wrote:

Hi Everyone

I have a spreadsheet which allows people to enter some figures in some cells.
Those cells then add together to creat bew totals.

I want to allow them to see all the individual lines or allow them to use
the group +or - (the rows have already been grouped) to change the views.

But, if I lock some cells in those rows, the Group + or - no longer works.

Is there anyway around? I've tried "Allow Format rows" as a protection
option but it didn't work. I've tried to select the whole worksheet, unlocked
it, then locked smalls parts, that didn't work either.

If there is a solution, I'd love to hear from you!

--

Dave Peterson

Protection - Allow Group/Ungroup but lock / unlock some cells
 

Hi Dave

Thanks and will have a go at getting that to work!
cheerio
LinLin

"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.)

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

LinLin wrote:

Hi Everyone

I have a spreadsheet which allows people to enter some figures in some cells.
Those cells then add together to creat bew totals.

I want to allow them to see all the individual lines or allow them to use
the group +or - (the rows have already been grouped) to change the views.

But, if I lock some cells in those rows, the Group + or - no longer works.

Is there anyway around? I've tried "Allow Format rows" as a protection
option but it didn't work. I've tried to select the whole worksheet, unlocked
it, then locked smalls parts, that didn't work either.

If there is a solution, I'd love to hear from you!

--

Dave Peterson