How to Disable Delete on a Worksheet in VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Disable Delete on a Worksheet in VBA
Delete controls in Excel using VBA. (Jupiterimages/ Images)

Disabling delete options in you Microsoft Office Excel application using Visual Basic for Applications, or VBA, can protect data in worksheets that should not be modified. Use the "Workbook_SheetBeforeRightClick" event to disable the "Delete" option from the menu bar. In VBA, you can also protect the worksheet by not allowing the user to delete any content using the "Delete" key on their keyboard.

Skill level:

Other People Are Reading


  1. 1

    Launch Microsoft Office Excel, and type "1" in "A1," "2" in "A2," "3" in "A3," "4" in "A4," and "5" in "A5." Click the "Developer" tab, and click "Visual Basic" to launch the VB Editor.

  2. 2

    Double-click "ThisWorkbook" from the Project Explorer pane to open the code module.

  3. 3

    Copy and paste the following code to disable "Delete" on the worksheet:

    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Dim cmdBCntrl As CommandBarControl
    Dim pos As Integer
    On Error Resume Next
         Sh.Protect UserInterFaceOnly:=True
    With Application.CommandBars("Cell")
        pos = .Controls("Delete...").Index
        Set cmdBCntrl = .Controls.Add(Before:=pos, Temporary:=True)
    End With
    On Error GoTo 0

    End Sub

  4. 4

    Switch back to Microsoft Excel, and try right-click on any cell. The menu will not include the "Delete" option. Press the "Delete" key on your keyboard and you will get a message stating that the cell is protected.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.