Share this content
4

Excel Query: Sheet Protection with Grouping Function still allowed

Excel Query: Sheet Protection with Grouping...

Didn't find your answer?

I'm looking for a way to protect certain sheets on a spreadsheet file, whilst still allowing the user to open and close "grouped" rows and columns. Anyone know if this is possible and how?

Thanks, Martin

Replies (4)

Please login or register to join the discussion.

avatar
By Richard Willis
12th Aug 2010 22:10

Simples!

Before protecting the sheet(s) highlight the cells that will need to be opened and go to Format Cells/Protection tab and remove the 'locked' tick.  Then protect the sheet(s).

Thanks (0)
avatar
By kkboadi
19th Aug 2010 16:09

protecting sheet with group function still working

I have tried it and its not working

Thanks (0)
avatar
By theaaman
19th Aug 2010 16:11

Excel Query: Sheet Protection with Grouping Function still allow

Martin

I'm not sure what version you using but from an Excel 2003 perspective the only way I know to allow grouping on protected sheets is via VBA in the "ThisWorkbook" section.

The snippet of code below should protect your worksheet when opened (providing macro's are enabled) and allow grouping with the "EnableOutlining" set to true.

Private Sub Workbook_Open()
   
    Dim wSheet As Worksheet
   
    If ActiveWorkbook.Name Like "Test*" Then
    Else
        For Each wSheet In Worksheets
           wSheet.Protect Password:="test", UserInterfaceOnly:=True
           wSheet.EnableOutlining = True
        Next wSheet
    End If
   
    Worksheets("Sheet1").Select
    Range("A3").Select

End Sub

The link at: http://www.ozgrid.com/Excel/outlining-protected.htm gives more information.

Not sure if you want to use such a forceful method to allow users to group / ungroup data.

Regards

Antony

Thanks (0)
avatar
By kkboadi
19th Aug 2010 16:50

Excel Query: Sheet Protection with Grouping Function still allow

I have tried it on excel 2003 and its working! I must say that it was a bit confusing

Thanks (0)
Share this content

Related posts