Protect shapes so users can modify but not delete

Question

I have a workbook with some shapes that I don't want users to delete easily. They are linked with an index, and the index number is linked with a whole other bunch of stuff.

However, the user needs to move the shapes around and resize them.

How can I set the protection on the sheet to allow users to modify the shapes but not delete them?

The ability to do this in VBA would be useful too, as a few of my macros unprotect and then protect cells.

possibly [this](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868) and override the Delete Method ? — , Jul 03 '13 at 09:30
I'm not sure how that helps me. Possibly because I can't follow it, but It's shapes I'm trying to protect, not cells. Can you explain a bit more? Thanks — mcallagain, Jul 03 '13 at 10:55
im not going to do your homework for you. the idea is similar - select shapes > right click -> format shape > properties > locked tick box. then protect sheet and you cant delete them but may be able to move them etc. another way its to use VBA to set what properties are locked and which ones are not — , Jul 03 '13 at 11:00

score 0 · Answer 1 · answered Jan 09 '14 at 09:12

This is not possible.

You can lock all shapes in the workbook by using this code

Private Sub Workbook_Open()
  ActiveSheet.Protect Password:="test", userinterfaceonly:=True
  Dim shape As shape
  For Each shape In ActiveSheet.Shapes
    shape.Locked = True
  Next
End Sub

or you build a plugin for Excel in dot.net. This blog describes how you would do that for shapes and there you can use events to overide the delete.

create excel events for shapes or Microsoft excel shape events

score 0 · Answer 2 · answered Oct 26 '22 at 22:59

This is indeed possible but with a slight workaround. No windows API. However, what are your shapes?

You can utilize ActiveX image controls and manipulate the MouseDown/MouseUp events to detect starting position and ending position via the left and top properties. Once calculating the difference in positions, you can set the image's new top/left properties to the appropriate numbers. You can even look into possible drag effects for the image so the cursor does not appearing totally still during a drag.
Alternatively, you can turn on that sheet's protection for editing objects which prevents moving/deleting innately. It still allows clicking, at which point you can assign a macro that reacts somewhat similarly to solution #1 but is more complex. Your macro can read the picture name by referencing the "application.caller" property. Each picture name can have different procedures associated with it. Read the current top and left properties of the shape. Continuing breaking the automation down into 2 steps. Programmatically unprotect the sheet, and monitor with a public boolean that an operation is in progress. Use the selection change event to monitor the next cell click at which point you can capture the subsequent top and left properties, eventually calculating where the shape will move. Programmatically protect worksheet again.

Protect shapes so users can modify but not delete

2 Answers2