Excel VBA Protect Sheet Allow Edit Objects

VBA & macros · Updated November 22, 2026

Yes, you can use Excel VBA to protect a worksheet while still allowing users to edit objects such as shapes, charts, text boxes, and form controls. The key is setting the AllowEditObject parameter of the Protect method to True (or using the UserInterfaceOnly parameter for a more flexible approach). For example, Worksheets("Sheet1").Protect Password:="secret", AllowEditObject:=True protects the sheet but leaves all objects editable.

How Sheet Protection Interacts with Objects

In Excel, sheet protection locks cells and other elements based on two factors: the Locked property of each cell or object, and the protection options you choose when you protect the sheet. By default, all cells have their Locked property set to True, and all shapes, charts, and controls have their Locked property set to True as well. When you protect a sheet, anything that is locked becomes uneditable. However, the Protect method has several optional parameters that let you override this for specific object types:

  • AllowEditObject – If True, all objects (shapes, charts, images, embedded controls) can be edited even if their Locked property is True. Default is False.
  • AllowEditScenarios – For scenarios, not objects.
  • UserInterfaceOnly – If True, the protection applies only to the user interface; VBA code can still modify cells and objects. This is often combined with AllowEditObject for a flexible setup.

Note: If you want to allow editing of only specific objects (e.g., one chart but not another), you cannot do that with a single protection call. You would need to set the Locked property of each object individually (via VBA or the Format Shape dialog) and then protect the sheet with AllowEditObject:=False (or leave it default).

Using the Protect Method in VBA

The Protect method syntax is:

Worksheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

But for objects, the key parameters are DrawingObjects and AllowEditObject – actually, in the VBA object model, DrawingObjects is a legacy parameter that, when set to True, locks objects (it’s the opposite of what you might expect). In modern Excel, you should use the named argument AllowEditObject for clarity. Here’s a complete example:

Sub ProtectSheetAllowObjects()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Protect the sheet, allow editing of objects, and also allow VBA to modify cells
    ws.Protect Password:="mypassword", AllowEditObject:=True, UserInterfaceOnly:=True
    
    MsgBox "Sheet protected, but objects are editable."
End Sub

Remember to save a copy of your workbook before running any macro, because protection changes cannot be undone with Ctrl+Z.

Allowing Edits via the Ribbon (Without VBA)

If you prefer not to use VBA, you can achieve the same effect manually:

  1. Go to the Review tab.
  2. Click Protect Sheet.
  3. In the dialog, check the box Edit objects under “Allow all users of this worksheet to:”.
  4. Optionally set a password.
  5. Click OK.

This is equivalent to setting AllowEditObject:=True. On Mac, the path is the same (Review > Protect Sheet) but the dialog may look slightly different.

Why Would You Want This?

Common scenarios include:

  • Dashboards: You protect the underlying data and formulas, but allow users to interact with slicers, buttons, or chart elements.
  • Forms: You have a userform-like layout with text boxes and combo boxes on the sheet; you want users to type in those fields but not change the labels or formulas.
  • Charts: You want to let users move or resize charts without altering the source data.
  • Comments: Though comments are not objects in the same sense, you can allow editing comments via the AllowEditComments parameter (but that’s separate from objects).

Without this setting, protecting a sheet freezes all objects, which can frustrate users who need to click a button or drag a slider.

Common Problems and Misconceptions

Problem: Objects are still locked even after setting AllowEditObject:=True. This can happen if the objects are actually ActiveX controls (like CommandButton from the Control Toolbox) rather than Form controls or shapes. ActiveX controls have their own Locked property that you must set to False individually. Also, if you used DrawingObjects:=True in the Protect call, that overrides AllowEditObject – avoid using DrawingObjects.

Misconception: UserInterfaceOnly:=True alone allows object editing. It does not. UserInterfaceOnly only allows VBA code to modify the sheet; it does not affect what a user can do manually. You must combine it with AllowEditObject:=True for that.

Problem: The protection is not applied when the workbook is opened. If you protect a sheet via VBA and then save the workbook, the protection is saved. But if you use UserInterfaceOnly:=True, that setting is not saved – you must reapply it every time the workbook opens (e.g., in the Workbook_Open event).

Alternatives to Sheet Protection for Objects

If you find that sheet protection is too restrictive or complex, consider these alternatives:

  • Lock individual objects: Right-click an object, select Size and Properties (or Format Shape), and uncheck Locked under the Protection tab. Then protect the sheet normally. This gives you fine-grained control.
  • Use a UserForm: Instead of placing editable controls on the sheet, create a UserForm for data entry. The sheet remains fully protected and inaccessible.
  • Use Excel Tables: If you want users to edit data in a specific range, you can protect the sheet but allow editing in a table by using the AllowEditRanges feature (Review > Allow Edit Ranges). This is not for objects but for cells.
  • Disable protection entirely: If you only need to prevent accidental changes, consider using the ReadOnlyRecommended property when saving the workbook, or simply inform users not to edit.

Frequently Asked Questions

Q: How do I allow editing of a specific chart but not others?
A: Set the Locked property of each chart individually. Right-click the chart, choose Format Chart Area, then go to Size & Properties > Properties and uncheck Locked. Do this for the charts you want editable, then protect the sheet with AllowEditObject:=False (or leave it default). Only unlocked objects will be editable.

Q: Can I allow editing of objects but not cells?
A: Yes. Protect the sheet with AllowEditObject:=True and do not enable any cell editing options like AllowFormattingCells or AllowInsertingRows. Users will be able to move/resize objects but not change cell values or formatting.

Q: Why does my VBA code fail to unprotect the sheet when it has a password?
A: You must supply the correct password in the Unprotect method. For example, ws.Unprotect Password:="mypassword". If you forget the password, you cannot unprotect the sheet without third-party tools, which is why it’s crucial to keep a copy of your workbook before protecting with a password.

Q: Does UserInterfaceOnly work on Excel for Mac?
A: Yes, it works, but as noted, it is not persisted when the workbook is saved. You need to reapply it in the Workbook_Open event. On Excel for the web, VBA is not supported at all, so you must use the manual ribbon method.

Q: Can I allow editing of objects while preventing deletion?
A: No, the AllowEditObject option does not distinguish between editing and deleting. If you allow editing, users can also delete objects. To prevent deletion, you would need to lock the object (set Locked to True) and not allow object editing, but then they can’t edit it either. There is no built-in way to allow moving but not deleting.

Conclusion

Protecting a sheet while allowing object edits is straightforward with the AllowEditObject parameter. For most cases, using Protect with AllowEditObject:=True is sufficient. If you need VBA to modify the sheet as well, add UserInterfaceOnly:=True, but remember to reapply it on workbook open. For more granular control, adjust the Locked property of individual objects before protecting. Always test on a copy of your workbook to avoid unintended lockouts.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.