CODEWITHSUNDEEP
vbaexcel
Eva FP
Eva FP

Reputation: 775

How to disable "Insert" Command in VBA

I need a macro to disable inserting cells, rows and columns. I have tried these unsuccessful tricks (among others):

Application.CommandBars("Cell").Enabled = False

ActiveSheet.Protect AllowInsertingRows:=False, AllowInsertingColumns:=False

Application.CommandBars("Worksheet Menu Bar").FindControl _
 (ID:=296, Recursive:=True).Enabled = False

CommandBars("Cell").FindControl(, 21437).Enabled = False

In each trial I have retrieved errors or just achieved disabling the row's inserting when selecting a column and vice versa. How can I get my goal?

Upvotes: 0

Views: 8284

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

you should be able to accomplish this by protecting your sheet with the right parameters,

try putting the below code in the object module for your worksheet (on the sheet tab in Excel, right-click and select View code):

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

     'ProtectSheet
    ActiveSheet.Protect AllowInsertingColumns:=False, AllowInsertingRows:=False, UserInterfaceOnly:=True         
End Sub

UPDATE: you can also do or undo this in Excel with menu TOOLS>>PROTECTION>>PROTECT SHEET or on the ribbon the button is in the Review tab.

EDIT: for how to disable specific menu commands and controls please see Excel MVP Ron de Bruin - Disable menus & controls

Upvotes: 1

Related Questions