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

excel vba disable insert row or column

Insert /Delete Rows

excel vba disable insert row or column

Insert /Delete Columns

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 vba disable insert row or column

Insert /Delete Rows

excel vba disable insert row or column

Insert /Delete Columns

excel vba disable insert row or column

 

You can find similar Excel Questions and Answer hereunder

1) How can I find the number of working days between 2 dates using VBA?

2) Line break in vba message box in Excel

3) How can I dynamically add series to an existing chart using VBA?

4) Highlight row of selected cell in Excel

5) How can I convert Column numbers into Column names for use in range definition?

6) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

7) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

8) How to create Pivot table in excel VBA

9) How can I hide Formula Bar and Headings using VBA?

10) How do i apply a formula to an entire column in Excel

 

Here the previous and next chapter