CODEWITHSUNDEEP
excelvbashapesspreadsheet-protection
mcallagain
mcallagain

Reputation: 41

Protect shapes so users can modify but not delete

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.

Upvotes: 4

Views: 10940

Answers (2)

Infrequent Coder
Infrequent Coder

Reputation: 99

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

  1. 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.

  2. 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.

Upvotes: 0

Archlight
Archlight

Reputation: 2089

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

Upvotes: 0

Related Questions