CODEWITHSUNDEEP
excelvba
Dave
Dave

Reputation: 37

Blocking charts from removing

I have a chart that I want user not to delete. Yet I'd like to give him option to move or resize it if he wants to, so I think, that option locked in properties is not appropriate here (I use secure worksheet too).

Is there any possibility here to make code that works when user clicks Del on the keyboard, then some msgBox with information with error, that this chart is not deletable appears and then discard this action of removing?

Upvotes: 2

Views: 246

Answers (1)

Samuel Hulla
Samuel Hulla

Reputation: 7089

It is impossible to prevent Chart deletion, while simultaneously enabling it to be moved

There are two following options, which *sorta* achieve what you want:

  1. Enable Sheet Protection (Review -> Protect Sheet) which will achieve the following:

    • prevent the Chart from being deleted
    • unfortunately also prevent chart from being moved

  2. Use Application.OnKey to detect press of Delete

    Private Sub Worksheet_Activate()
    Application.OnKey "{DELETE}", "PreventDeletion"
End Sub

Private Sub PreventDeletion()
    MsgBox ("Deletion not allowed in this worksheet")
End Sub
    • this allows the chart to be moved
    • unfortunately only creates an illusion of delete protection. If user uses mouse2 and selects the delete option, it will still be available to him. This only interrupts pressing the Delete
    • also this renders Delete obsolete for entire Sheet, which can be annoying (for the users)

Neither option is ideal, though personally I prefer the first one, as preventing usage of Delete altogether is bound to annoy your users.

Upvotes: 1

Related Questions