Vba disable insert row or column in Excel
For example, I have a complex WorkSheet where functionality is likely to be affected by some user actions like "Insert or Delete Rows/Columns/Cells". In Excel, how can I disable the ability to insert / delete Rows/Columns/Cells?
When right clicking on a Sheet, Shortcut Menu has "Insert" and "Delete" options as shown below. These options have to be disabled.
Insert /Delete Cell
Insert /Delete Rows
Insert /Delete Columns
To do it in Excel, here is the answer:
- Option Explicit
- Sub PreventInsertDeleteRowsCols()
- Dim ctrl As CommandBarControl
- ''
- 'Disable "Row" Delete.
- For Each ctrl In Application.CommandBars.FindControls(ID:=293)
- ctrl.Enabled = False
- Next ctrl
- 'Disable "Column" Delete.
- For Each ctrl In Application.CommandBars.FindControls(ID:=294)
- ctrl.Enabled = False
- Next ctrl
- ''
- 'Disable "Row" and "Column" Insert.
- For Each ctrl In Application.CommandBars.FindControls(ID:=3183)
- ctrl.Enabled = False
- Next ctrl
- ''
- 'Disable "Cell" Delete.
- For Each ctrl In Application.CommandBars.FindControls(ID:=292)
- ctrl.Enabled = False
- Next ctrl
- 'Disable "Cell" Insert.
- For Each ctrl In Application.CommandBars.FindControls(ID:=3181)
- ctrl.Enabled = False
- Next ctrl
- End Sub
Description:
a) The setting applies to the entire Excel Application and not just to the current WorkBook. Hence care should be taken to resetting the controls as appropriate (like resetting them before closure using "WorkBook_BeforeClose" event).
b) To enable the controls again, set the " .Enabled" for the control to TRUE.
c) The link below is a good starting point to get the IDs of different controls in Command Bar.
https://support.microsoft.com/en-us/help/213552/list-of-id-numbers-for-built-in-commandbar-controls-in-excel-2000
Result after Macro Execution
Insert /Delete Cell
Insert /Delete Rows
Insert /Delete Columns