Excel VBA: Can I enable only one shape for inserting into a cell & disable all others

Hi,
I would like the user to be able to insert only one shape inside a cell. Therefore, I'd like to disable all other shapes and/or objects. Preference for enabled shape would be something simple, eg; rectangle.

I don't think you need to know what VBA code I already have but here is an example for the workbook just in case:
 With Sheet1 'AO5 tech
       .Protect Password:="password", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, DrawingObjects:=False, Contents:=True, Scenarios:= _
        True
       .EnableOutlining = True
    End With
    

Open in new window


Hope this makes sense!
kazmdavAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
If you set DrawingObjects:=True when you protect the worksheet, the user will not be able to insert a shape. But you can do so with a macro.
Sub Protector()
With Sheet1 'AO5 tech
       .Protect Password:="password", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, _
            AllowInsertingHyperlinks:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
       .EnableOutlining = True
    End With

End Sub

Sub NewShape()
ActiveSheet.Shapes.AddShape msoShapeRectangle, ActiveCell.Left, ActiveCell.Top, ActiveCell.Width * 0.9, ActiveCell.RowHeight * 0.9
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.