Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA Allow features in a protected worksheet 1

Status
Not open for further replies.

MM1963

Vendor
Feb 15, 2018
38
US
I’m looking to protect a worksheet, but allow grouped columns to be collapsed or expanded. Also would like to allow the user to set filters and hide columns. I put the below code in the “ThisWorkbook” object. This protected the worksheet but not with a password. The VBA below allowed the grouped columns to expand and collapse. It also allowed columns to be hidden, but did not allow filtering. Not an expert with VBA so looking for help. Also, if I decide to password protect, where would I enter that in the VBA?
Thanks for your help.

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect UserInterfaceOnly:=True
.Protect AllowFiltering:=True
.Protect AllowFormattingColumns:=True
End With
End Sub
 
>but did not allow filtering

SWell,. it does - but only if you've already got a simple filter, which Microsoft often refer to as an autofilter (basically the filter applied when you click the funnel icon) in place; you can change it. And it allows you to apply a n Advanced filter. What you can't do is ADD a NEW simple filter. Or, as the MS documentations says:

Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter
 
Thanks for the reply. I created filters in the first few columns. When I reopened the file, the filters were there but did not function. I clicked on the filter arrow and there is no drop down.
 
'Protect' method has some optional arguments, password among others, you can use them in single line:
[pre]With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="YourPassword", UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True
End With[/pre]

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top