Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2007

Tip: Looking for answers? Try searching our database.

Grouping and Protection

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tony Betley - 28 Mar 2007 01:20 GMT
I need to use grouping on a spreadsheet (Excel 2003). Having designed the
spreadsheet which uses grouping I then want to protect it but in doing so it
stops users showing and hiding the groups.
I have tried variuos options within the protect sheet dialog box but to no
avail.
Any assistance appreciated.
Thanks
tony
Dave Peterson - 28 Mar 2007 13:09 GMT
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
   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

> I need to use grouping on a spreadsheet (Excel 2003). Having designed the
> spreadsheet which uses grouping I then want to protect it but in doing so it
[quoted text clipped - 4 lines]
> Thanks
> tony

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.