Custom context menus for any WB (using add-in?)

**zeke varg** · 01-12-2010, 04:31 PM

Ok, so I have this neat little macro that on right-click brings up a custom context menu that displays a few different options - for example one such function is simply copying the selected cell content to a (specific) different sheet. It is a very useful feature in the context I am normally using it to merge information from different locations in a new structure (well, I think this is irrelevant, but just letting you know basically what I am doing).

Anyhow, to implement the custom context menu, I am using events in the ThisWorkbook object, namely Activate to add context menu entries and DeActivate to remove them if switching workbook.

Now, if I receive any workbook from someone else, I'd like to add this feature to get the custom context menu easily without having to copy the received workbook into a new workbook (it's a bit of a hassle, and ideally, if the received workbook has any other macros, this function could just be merged). I was thinking using an add-in to achieve this, but using the ThisWorkbook events in the add-in doesn't trigger the events (I guess because the hidden workbook in the add-in is never "Activated" or "DeActivated").

Does anyone have any idea of how to simply be able to turn on and off a custom context menu for any workbook? Any help much appreciated!

- zeke

**Leith Ross** · 01-12-2010, 05:33 PM

Hello Zeke,

If your Add-In modifies the Excel command bar "Worksheet Menu Bar" context menu "Cell" then it will be available to all workbooks. You can have the Add-In make the changes when it is loaded and remove the changes when it closes. What approach have you taken?

**zeke varg** · 01-12-2010, 05:50 PM

Well, originally (before attempting add-in) I just used Workbook_Activate and Workbook_DeActivate to add and remove custom items from the Application.CommandBars("cell").

At first, when creating the add-in, i just moved the code to the add-in using the same events. When that didn't work I moved it to Workbook_AddinInstall and Workbook_AddinUnInstall, which I didn't manage to get working either.

The code below is what I put into ThisWorkbook object of the add-in (which doesn't work for me).

Code:

Private Sub Workbook_AddinInstall()
    Dim cbc As CommandBarControls 'CommandBarControls object
    Dim cbpop As CommandBarPopup
    Set cbc = Application.CommandBars("cell").Controls

'1. Delete any submenu
    Application.CommandBars("Cell").Reset

'2. CREATE TakeAction MENU
    Set cbpop = Application.CommandBars("cell").Controls.Add(msoControlPopup, , , 1)
    cbpop.Tag = "MyCustomSubmenu"
    cbpop.Caption = "Take &Action"
    
'3. ADD ITEMS FOR COMPLETE WORKBOOK
    With cbpop.Controls.Add(Type:=msoControlButton)
    .Caption = "&New Comment"
    '.FaceId = 420
    .OnAction = "shtComments.newComment"
    End With
    
    With cbpop.Controls.Add(Type:=msoControlButton)
    .Caption = "&View Comments"
    '.FaceId = 420
    .OnAction = "shtComments.viewComments"
    End With

End Sub

Private Sub Workbook_AddinUninstall()
    Application.CommandBars("Cell").Reset 'Remove any submenu
End Sub

**romperstomper** · 01-12-2010, 06:34 PM

That code should work (as long as it's not being overridden by some other code, such as that in your existing workbook, which resets the commandbar) but is only triggered when you install the addin via the addins dialog.. You should also be aware that there are 2 Cell commandbars - it's safer to modify both rather than just one.
As an alternative you could leave the commandbar untouched and use an application object variable declared withevents, trap the beforerightclick event for all worksheets, and create and present your own menu instead of the default one. If you are interested in that, I can put something together for it.

**zeke varg** · 01-12-2010, 06:55 PM

Ahh, now THAT's a great idea! I saw it somewhere and did a quick and dirty try, but as I never messed around with creating custom application objects, I blatantly failed!

If you could be so kind and give me an example of how to achieve that, I'd be much obliged!

EDIT: The code posted above does work indeed! The key was, as romperstomper pointed out, to reactivate the add-in (I probably had it activated when the code was added, so it was never triggered). Thanks! I'd still very much like to see how it may be possible to realise with a cusom application object - it sounds like a nice and clean solution.

**romperstomper** · 01-12-2010, 07:44 PM

Here's a very rough sample add-in (had to zip it to upload).
Any questions, let me know!

**zeke varg** · 01-13-2010, 02:54 PM

Thanks alot for that! Looks pretty clean, nice and flexible. As always, a topic is tons easier to understand by example. I'll play around with it for a while, meanwhile I guess I'll close the thread.

If someone wants to get a solution idea, at least using an add-in and putting the code in AddinInitialize and AddinUninitialize events worked, just making sure that the add-in is initialized AFTER the code is added and module saved!

The method proposed using a custom application object with events seems to have great potential for solving the problem as well, however I have yet to play around with it for a while.

Thanks!!!