×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Group/Ungroup a Protected Excel Sheet

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

(OP)
The code that I used to collapse the groups is presented below.  This collapses every group, which isn't what is desired but is better than nothing...  

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources