PDA

View Full Version : Right click option menu...specific items



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

mikerickson
08-04-2012, 08:47 AM
In regards to Application.CommandBars("Cell").Reset How do you re-apply the users customization of their toolbars after they are done with your workbook and want to use their other workbook(s)?

Do you want these changes to Excel CommandBars to remain when the user switches between your workbook and some other workbook?

tx7399
08-04-2012, 09:12 AM
This code enables the right click options in other workbooks after this workbook is deactivated.
Private Sub Workbook_Deactivate()

Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").Enabled = True

Application.ShowMenuFloaties = False 'turns ON the mini toolbar

End Sub

Aflatoon
08-06-2012, 06:21 AM
But it does not reset any changes the user (or their addins) might have made to the right-click menu - it simply resets the menus to Excel's defaults. Why not use the beforerightclick event instead?