This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I'm a novice to macros and previously found help on this site to write a macro that will allow grouped rows and columns to be expanded and collapsed on 2 protected worksheets in my workbook while retaining the protection parameters that I originally selected. This is the macro I ended up using:
Private Sub Workbook_Open()
With Worksheets(“Tab1”)
.Unprotect "password"
.EnableOutlining = True
.Protect "password", DrawingObjects:=True, contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, userInterfaceOnly:=True
End With
With Worksheets(“Tab2”)
.Unprotect "password"
.EnableOutlining = True
.Protect "password", DrawingObjects:=True, contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, userInterfaceOnly:=True
End With
End Sub
I want to add Edit Objects to the list of protection parameters that remains checked so I tried adding AllowEditingObjects:=True, to the code, but I now get a debug error message when I open the workbook.
Is it possible to add that parameter to the list and, if so, can someone provide the proper code to do so?
Thank you!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Try changing DrawingObjects:=True to False
Added. . . . .quite possibly I have misunderstood the request.
Do you want to edit or not edit the objects?
Gord
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Thanks for your response Gord. I must admit I'm not sure what impact 'DrawingObjects=True' has in the macro. My initial request was for help to allow the 'Format cells', 'Format columns', and 'Format rows' Protect Sheet parameters to remain checked (as well as the 'Select locked cells and 'Select unlocked cells' default parameters) when the Protect command was run by the macro. The answer that was provided on this forum and that worked in my workbook was this:
Private Sub Workbook_Open()
With Worksheets(“Tab1”)
.Unprotect "password"
.EnableOutlining = True
.Protect "password", DrawingObjects:=True, contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, userInterfaceOnly:=True
End With
With Worksheets(“Tab2”)
.Unprotect "password"
.EnableOutlining = True
.Protect "password", DrawingObjects:=True, contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, userInterfaceOnly:=True
End With
End Sub
Now I also want to be able to add comments to protected cells, and my research tells me that the 'Edit Objects' box needs to be checked when the sheet is protected. I hope that long explanation helps to clarify my request!
Thank you Gord, that solved my problem!