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
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.
Double-click "ThisWorkbook" from the Project Explorer pane to open the code module.
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
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for