2

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.

Community
  • 1
  • 1
mcallagain
  • 21
  • 1
  • 3
  • 1
    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

1 Answers1

0

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

Archlight
  • 1,929
  • 2
  • 20
  • 31