Tableobject disable delete option or menu

[bwbrandiwoodson]

I have successfully greyed out delete and insert options using vba on rows on right click on a worksheet. However, i have a table on this worksheet that stores the data we share and if you right click insert and delete are still an option for shared users. I cannot protect as excel will not allow for new rows to be instead into the table. I need a code if possible to grey out either the delete and clear contents options on right click table menu or disable table menu all together but no luck finding a code for it online. I'm Ok with insert within the table options... not insert on worksheet itself.

Solution needed to disable table menu options. I tried advanced settings and nothing is working!

 

[GWteB]

Hi bwbrandiwoodson, welcome to MrExcel!

This code will disable the right click context menu. To be pasted in the module of the worksheet to be affected.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

 

[bwbrandiwoodson]

Hi bwbrandiwoodson, welcome to MrExcel!

This code will disable the right click context menu. To be pasted in the module of the worksheet to be affected.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

I need a code that disables the table menu only not the context menu on a worksheet itself for any cell within worksheet. A user can right click and past special into next row and once something is inserted it cares a table row automatically. But thank you. Users still need the option to right click and past special in a new row. I just dont want rows deleted!

 

[GWteB]

I see. Perhaps you're looking for something like this.

Public Sub Usage_Example()

    ' disable some delete options within context menus
    Call Set_Delete
    ' re-enable those delete options before workbook closes
    Call Set_Delete(True)
End Sub

Public Sub Set_Delete(Optional argEnable As Boolean = False)

    Dim menuCtrls   As CommandBarControls
    Dim ctrl        As CommandBarControl
    Dim arr()       As Variant
    Dim idx         As Variant

    arr = Array(292, 293, 294, 7374, 7569)
    For Each idx In arr
        Set menuCtrls = Application.CommandBars.FindControls(ID:=idx)
        If Not menuCtrls Is Nothing Then
            For Each ctrl In menuCtrls
                ctrl.Enabled = argEnable
            Next ctrl
        End If
    Next idx
End Sub