Prevent row and column insert and delete and prevent clear contents |VBA & Macros|Excel Forum|My Online Training Hub

Hi -

I have a worksheet with a pivot table that starts in cell W1. It's important that the table does not move to a different column or row, because the specific cell address "W2" is referenced in multiple formulas. The table is located in hidden cells. 

I need to prevent the user from inserting or deleting rows or columns through any mechanism. Here is what I have so far, but I think I must be missing something my more "creative" users may attempt.

Also, I can't seem to disable the "Clear Contents" option. 

Any thoughts?

Sub AllowInsertDeleteRowCols(bSwitch As Boolean)

  Dim ctrl As CommandBarControl

  ' Row delete
  For Each ctrl In Application.CommandBars.FindControls(ID:=293)
    ctrl.Enabled = bSwitch
  Next ctrl

  ' Column delete
  For Each ctrl In Application.CommandBars.FindControls(ID:=294)
    ctrl.Enabled = bSwitch
  Next ctrl

  ' Row and column insert
  For Each ctrl In Application.CommandBars.FindControls(ID:=3183)
    ctrl.Enabled = bSwitch
  Next ctrl

  ' Cell delete
  For Each ctrl In Application.CommandBars.FindControls(ID:=292)
    ctrl.Enabled = bSwitch
  Next ctrl

  ' Cell insert
  For Each ctrl In Application.CommandBars.FindControls(ID:=3181)
    ctrl.Enabled = bSwitch
  Next ctrl

  ' Clear contents
  For Each ctrl In Application.CommandBars.FindControls(ID:=873)
    ctrl.Enabled = bSwitch
  Next ctrl

  If bSwitch Then
    Application.OnKey "{DELETE}"
  Else
    Application.OnKey "{DELETE}", ""
  End If

End Sub


Sub DisableActions

  Call AllowInsertDeleteRowCols(False)

End Sub


Sub EnableActions

  Call AllowInsertDeleteRowCols(True)

End Sub