We help IT Professionals succeed at work.

enable formatting rows through Thsworkbook VBA code

91 Views
Last Modified: 2017-03-14
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

Rob HensonFinance Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
Distinguished Expert 2018

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