Group/Ungroup a Protected Excel Sheet
Group/Ungroup a Protected Excel Sheet
(OP)
I'm using Grouped Data in an Excel sheet to allow parts of the spreadsheet to be "collapsed" when they aren't needed. The sheet contains nested groups, and some or all of the data may or may not need to be viewed. This works great, but if I protect the sheet, I can't use the +/- buttons to view/hide data. I wrote a macro that will completely expand or collapse the groups in the sheet, but that really doesn't do what I need: I'd like to protect the cells, but I'd also like to be able to use the +/- buttons to expand/collapse what I want to see. Any ideas on a way around this?...





RE: Group/Ungroup a Protected Excel Sheet
Sub Collapse()
Dim blnProtection As Boolean
'Unprotect the sheet, collapse the outline, then re-protect
blnProtection = ActiveSheet.ProtectContents 'true if the sheet is protected
If blnProtection = True Then 'unprotect, collapse and re-protect
ActiveSheet.Unprotect
ActiveSheet.Outline.ShowLevels rowlevels:=1
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else 'just collapse
ActiveSheet.Outline.ShowLevels rowlevels:=1
End If
End Sub
This code "remembers" if the sheet was protected, and if so restores it to protected status, or otherwise just collapses the groups. The +/- functionality of excel doesn't appear to be exposed in VB.