vba disable insert row or column in Excel

Q147. In Excel, how to disable ability to insert Rows and Columns in Excel (using VBA)?

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

excel vba disable insert row or column

Insert /Delete Rows

excel excel vba disable insert row or column

Insert /Delete Columns

excel excel excel vba disable insert row or column

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub PreventInsertDeleteRowsCols()
  3. Dim ctrl As CommandBarControl
  4. ''
  5. 'Disable "Row" Delete.
  6. For Each ctrl In Application.CommandBars.FindControls(ID:=293)
  7. ctrl.Enabled = False
  8. Next ctrl
  9. 'Disable "Column" Delete.
  10. For Each ctrl In Application.CommandBars.FindControls(ID:=294)
  11. ctrl.Enabled = False
  12. Next ctrl
  13. ''
  14. 'Disable "Row" and "Column" Insert.
  15. For Each ctrl In Application.CommandBars.FindControls(ID:=3183)
  16. ctrl.Enabled = False
  17. Next ctrl
  18. ''
  19. 'Disable "Cell" Delete.
  20. For Each ctrl In Application.CommandBars.FindControls(ID:=292)
  21. ctrl.Enabled = False
  22. Next ctrl
  23. 'Disable "Cell" Insert.
  24. For Each ctrl In Application.CommandBars.FindControls(ID:=3181)
  25. ctrl.Enabled = False
  26. Next ctrl
  27. 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

excel excel excel excel vba disable insert row or column

Insert /Delete Rows

excel excel excel excel excel vba disable insert row or column

Insert /Delete Columns

excel excel excel excel excel excel vba disable insert row or column

 

You can find similar Excel Questions and Answer hereunder

1) How can I sort data using VBA?

2) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

3) How can I avoid updates to cell values during macro execution?

4) How to read a value from a cell in vba in Excel

5) Vba code to password protect workbook in Excel

6) How do i put double quotes in a string in vba in Excel

7) How can I dynamically add a hyperlink using VBA?

8) String split in vba in Excel

9) How do I assign a macro to a shape using VBA?

10) Vba clear the contents of an entire sheet in Excel

11) How can I find the last used cell in a Column in VBA?

12) How can I check if a file exists in a folder using VBA?

13) Vba length of an array in Excel

14) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

15) How can I loop through all WorkSheets and get their names using VBA?