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/Photos.com/Getty 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:
Moderate

Other People Are Reading

Instructions

  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)
    
        .Controls("Delete...").Delete
    
    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

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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