How to Distribute Custom Menus in Excel 1

[jjjjjjohn]

Hi there,

I have developed an Add-In which I would like to distribute throughout the company. On my computer I have developed a custom menu which calls the various macros in the add-in. The menu items also have icons.

I have figured out how to distribute the add-in itself but I can't figure out how to distribute the custom menu that calls the macros in it.

I know I could programatically generate a custom menu with "Application.CommandBars("Cell").Controls.Add" and so on but there are two disadvantages with doing it like this and I would like to know if there is an alternative way to distribute the menus. The disadvantages are the following:
- I haven't found a way to get the custom icons to appear in the custom menu when building it programatically.
- If possible I would like to avoid the need to execute the menu bilding code every time a user starts Excel (assuming they have the add-in installed).

I assume that an alternative exists because when I make the menu by hand on my computer there is no code involved. As such, the menu definition (along with the custom icons) must be stored somewhere on my system.

Does anyone know WHERE these menu definitions are stored? Can I simply make a copy of this file, put it in the coresponding directory on the user's computer so that the menu (along with the custom icons) is built in this way?

Thank you in advance for any help anyone can provide.

John


PS: I am using Excel 2003

 

[jjjjjjohn]

Hi Gavin and Markus,

I was thinking the same thing so I also tried adding it to a standard menu. Since I wasn't sure what the name of the standard menu was I just used the index number instead of the control name.

Application.CommandBars("Custom Toolbar").Controls("Budget Tools").Move Bar:= _
Application.CommandBars("Worksheet Menu Bar").Controls(1), Before:=1

But this generated the same error message.

To make sure I was working with valid objects I ran:

MsgBox Application.CommandBars("Worksheet Menu Bar").Controls(1).Caption

and got "&File". So it looks like it recognizes the target object and I know the source object is valid because I already added it to the Worksheet Menu Bar in an earlier attempt.

Any thoughts?

I also checked in the Excel Object Browser. There is a listing for the CommandBar object and this listing confirms that Controls is a member of the CommandBar object. But there is no listing for a Control object and hence no information about the Move method belonging to the Control object.

Any ideas where else I could look?

Thanks,
John

 

[Markus4]

Hi jjjjjohn,

I 've checked Excel help and there's nothing, absolutely nothing. So I had to experiment and look for another workaround. An attempt to find out where the type mismatch actually occurs was:

Sub Test()
Dim x As CommandBar
Set x = Application.CommandBars("Worksheet Menu Bar")[b][i].Controls(1)[/i][/b]
Application.CommandBars("Custom Toolbar").Controls("Budget Tools").Copy Bar:=x, Before:=1
End Sub

Then I thought that playing with the object type assigned to x might do the trick another time. No avail. So:

Excel seems to be very strict in the MOVE or COPY commands. A valid commandbar seems to be expected, nothing else. Thus, if you run the above code you get the type mismatch error in the line with the bolded text. If you omit the bolded text, no error is raised and the sub-menu is created in the menu bar.

Sorry. There seems to be no other way of tricking Excel with its own object model. It appears to me that you will have to put up with that
a) you can distribute your custom menu if you create it in a workbook on a user-defined commandbar and copy/move it to the user's menu bar
b) but you will not be able to move it into an existing sub-menu on your user's menu bar.

I've also played with creating a button in one of those sub-menus and 've tried to copy the user-defined menu to this. But I failed.

But alas*. If you spend such a lot of time and energy on making it comfortable for your users to do their work with Excel why shouldn't they know it? If 'budget tools' sub men is added to the worksheet menu bar they see it all the time. Maybe someone appreciates it. I do**.

Regards,

Markus
_________
* I hope I use 'alas' here correctly. Comments are appreciated.
** Hopefully I'm not giving up too early.

 

[Markus4]

Hi jjjjjohn,

today I thought that late binding* might do the trick. I tried, but no avail. Sorry.

Regards,

Markus
______________
* Instead of "Dim x as CommandBar" I used "Dim x as Object", what I think this is called late binding.

 

[jjjjjjohn]

Hi Markus,

Your and Gavin's help have been fantastic and I really do appreciate it. We didn't get all of what I was hoping to accomplish but we did get 95% of it and that is a lot more than I would have gotten on my own.

I think there must be a way to automatically build the drop-down menus with custom icons because I have seen this when I have installed some other software that had a plug-in for Excel.

But for my needs I have enough to do what I want. In fact I installed the add-in and built the menu for a couple of users already and they were pretty impressed.

Thanks a lot and kind regards,
John


PS: If I ever figure out how to do it 100% automated, I'll be sure to add it to this post so others can benefit from it