Tableobject disable delete option or menu

bwbrandiwoodson

New Member
Joined
Jan 19, 2021
Messages
2
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub
 

bwbrandiwoodson

New Member
Joined
Jan 19, 2021
Messages
2
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I see. Perhaps you're looking for something like this.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,700
Messages
5,626,386
Members
416,180
Latest member
Excel_user12321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top