asked on

enable formatting rows through Thsworkbook VBA code

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

Rob Henson

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

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rob heibel

ASKER

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

rob heibel

ASKER

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

Shums Faruk

You're Welcome Rob! Glad it worked.