enable formatting rows through Thsworkbook VBA code

rob heibel
rob heibel used Ask the Experts™
on
I originally wrote code in ThisWorkbook to allow the user to use the grouping function in a protected worksheet using the following code.

Private Sub Workbook_Open()
With Worksheets("Jobs By Pursuit Manager")
       .Protect Password:="pursuits1", Userinterfaceonly:=True
       .EnableOutlining = True
End With
End Sub

I then realized that even if I saved the file with autofilters and formatting rows allowed for the sheet, these attributes were removed upon opening the file.  

I was able to add .EnablAutoFilter = True to the code, as shown below, but I cant seem to get the allowance of formatting rows to work.  How do I add this to my code?

Private Sub Workbook_Open()
With Worksheets("Jobs By Pursuit Manager")
       .Protect Password:="pursuits1", Userinterfaceonly:=True
       .EnableOutlining = True
       .EnableAutoFilter = True
End With
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
Try this:

Private Sub Workbook_Open()
With Worksheets("Jobs By Pursuit Manager")
       .Protect Password:="pursuits1", Userinterfaceonly:=True
       .EnableOutlining = True
       .EnableAutoFilter = True
       .AllowFormattingRows:=True
End With
End Sub

Open in new window

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Try below:
Private Sub Workbook_Open()
Dim Ws As Worksheet
Dim strPassword As String
Set Ws = worksh("Jobs By Pursuit Manager")
strPassword = "pursuits1"
With Ws
    .Protect Password:=strPassword, Contents:=True, Userinterfaceonly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, DrawingObjects:=True, Scenarios:=True
    .EnableOutlining = True
    .EnableAutoFilter = True
End With
End Sub

Open in new window

Author

Commented:
Rob, using  ".AllowFormattingRows:=True"  creates a Compile error: "Expected: expression".

By using my original code, but then using Schum's technique of adding ", AllowFormattingRows"  after userinterfaceonly:=True, it works perfectly.  I ended up using the following:

Private Sub Workbook_Open()
With Worksheets("Jobs By Pursuit Manager")
       .Protect Password:="pursuits1", Userinterfaceonly:=True, AllowFormattingRows:=True
       .EnableOutlining = True
       .EnableAutoFilter = True
End With

End Sub

Author

Commented:
By using my original code, but then using Schum's technique of adding ", AllowFormattingRows"  after userinterfaceonly:=True, it works perfectly.  I ended up using the following:

Private Sub Workbook_Open()
With Worksheets("Jobs By Pursuit Manager")
       .Protect Password:="pursuits1", Userinterfaceonly:=True, AllowFormattingRows:=True
       .EnableOutlining = True
       .EnableAutoFilter = True
End With

End Sub
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Rob! Glad it worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial