tx7399
08-04-2012, 08:35 AM
Hi everyone,
I am using the following code to limit right click menu options to Cut, Copy, Paste, and Clear Contents. It works fine EXCEPT that a double click on a cell followed by a right click allows two options that I don't want:
Format Cells...
Pick From Drop Down List...
I want the user to be able to edit text only.
Can anyone help me disable the unwanted options at the workbook level?
And, of course, explain how I turn them back on when workbook closes.
Private Sub Workbook_Activate()
Dim icbc As Object
On Error Resume Next
Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").Enabled = True
Application.ShowMenuFloaties = True 'turns OFF the floating mini toolbar
For Each icbc In Application.CommandBars("Cell").Controls
If icbc.ID = 755 Or icbc.ID = 295 Or icbc.ID = 292 _
Or icbc.ID = 31402 Or icbc.ID = 31435 Or icbc.ID = 2031 Or icbc.ID = 1592 Or icbc.ID = 855 _
Or icbc.ID = 1966 Or icbc.ID = 1576 Or icbc.ID = 13380 Or icbc.ID = 30005 Then icbc.Delete
Next icbc
End Sub
I am using the following code to limit right click menu options to Cut, Copy, Paste, and Clear Contents. It works fine EXCEPT that a double click on a cell followed by a right click allows two options that I don't want:
Format Cells...
Pick From Drop Down List...
I want the user to be able to edit text only.
Can anyone help me disable the unwanted options at the workbook level?
And, of course, explain how I turn them back on when workbook closes.
Private Sub Workbook_Activate()
Dim icbc As Object
On Error Resume Next
Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").Enabled = True
Application.ShowMenuFloaties = True 'turns OFF the floating mini toolbar
For Each icbc In Application.CommandBars("Cell").Controls
If icbc.ID = 755 Or icbc.ID = 295 Or icbc.ID = 292 _
Or icbc.ID = 31402 Or icbc.ID = 31435 Or icbc.ID = 2031 Or icbc.ID = 1592 Or icbc.ID = 855 _
Or icbc.ID = 1966 Or icbc.ID = 1576 Or icbc.ID = 13380 Or icbc.ID = 30005 Then icbc.Delete
Next icbc
End Sub