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

 

[Gavona]

I believe that menu definitions are in the *.xlb file - but along with other customisation and you would not want to mess up user's own customisation.

I use the following to make my macros available to me via menu. The menu building does not appear to slow things down. You can specify FaceID to determine the ICON but of course this isn't a custom Icon. There are other bits of code on the same site (Excel Examples include several menu related).

http://www.bygsoftware.com/Excel/InterFace/menu_maker.htm

Gavin

 

[jjjjjjohn]

Thanks for the info Gavin,

I found the .xlb file on my computer but I'm disappointed that I can't export/import only the settings I want.

Do you happen to know exactly which settings are stored in the .xlb? I'm distributing this add-in to only about 10 users so I was thinking I could try changing the .xlb file one computer at a time. The users are pretty basic and most just use the default/installation settings anyway. But I figure I should know WHAT settings I will be changing on the user's machine before I do anything.

I'm assuming I can just replace the .xlb file on the user's maching with the one from my machine and if there are any problems/complaints then I can always replace the old one and look for another solution.

Do you forsee any problems with me doing this?

Also, do you happen to know if this limitation (that INDIVIDUAL settings can not be imported/exported) exists in Excel 2007?

Thanks once again for the help!

Cheers,
John

 

[Gavona]

Personally I would use a macro to create the new toolbar and use one of the thousands of standard faceids - live without the customised faceIDs/Icons.

Failing that the only problem I see is that you may lose a user's own customisations.

If you find a solution then do post it.

Sorry no idea about 2007.

Working with command bars is common when designing user-friendly Office applications. However, it is equally common to want images on command bar buttons rather than just text. Before creating a custom image for a button face, it is a good idea to see if there is already one that suits your needs. Chances are that among the literally thousands of button faces there is one that works for your application.

http://msdn.microsoft.com/en-us/library/aa159658(office.11).aspx

Gavin

 

[Markus4]

Hi,

as I have done my very special own private customizations of Word (no really complicated things, but very handy for me), I also have painted my own private custom icons.
This took (as I'm not at all a professional) a long time and I didn't want to possibly lose the work. So I created a template (using Word97) with a toolbar with the sole purpose to keep the custom icons.

Thinking of this and your problem I am just pondering:
would it be possible
- to deliver a custom toolbar containing all the custom icons,
- create the menu (with Menu Maker) and
- assign the custom icon?

To find out how to do this in VBA I tried to record a macro. But whatever I tried nothing was recorded. Maybe this could possibly be helpful even though I cannot offer any solution.

M.
_______
fumei: I had to notice (in the Word2007 header post) that I have a tendency not to read other posts precisely and not be clear in my own posts. I am trying to stop that in the future.

 

[fumei]

fumei: I had to notice (in the Word2007 header post) that I have a tendency not to read other posts precisely and not be clear in my own posts. I am trying to stop that in the future.

That is very polite of you, and in the long run doing so will only benefit your own questions.

Good one.

Gerry

 

[Gavona]

OK, I found a solution to sharing your custom toolbar with custom icons while not affecting existing toolbar customisations:

In short you attach your toolbar to a workbook (customise, toolbars,attach). When that workbook is opened the custom toolbar will be added provided that no toolbar of the same name already exists.

Several options from there:
If you close your workbook then the toolbar remains.
If you close Excel then the new toolbar is saved with Excel in the xlb file.
Or you can write code which means that the toolbar will only appear while your workbook is open (it can be open but all worksheets "hidden" or "veryhidden")

I like the idea of distributing code so that when users open Excel, or click on your toolbar a readonly workbook from a network location opens. You can modify this workbook as you develop further code to share and it is distributed with no further actions on your part. Not sure if distributing as an add-in is as flexible.
To achieve this you 'just' need to distribute some code that creates an on-open event in Personal.xls that opens your workbook.

Hope this helps.

Gavin

 

[Markus4]

Hi,

yes, Gavona, thank you, I think we've got it.

I just tried the following:
I created a sub-menu on a user-defined commandbar ("Benutzerdefiniert"), appended it to a normal worksheet and saved this in a folder.
I told Excel to look in this folder for Workbooks to load with Tools - Options - Tab: General, Additional Start-up folder (or similar).
With

Application.CommandBars("Benutzerdefiniert").Controls(1).Move Bar:= _
Application.CommandBars("Worksheet Menu Bar"), Before:=10

in an AutoOpen procedure or in the Workbook_Open event this sub-menu can be moved to everey desired location (here after "Window").

Regards,

Markus

 

[Markus4]

Hi,

the suggested workbook with the user-defined commandbar containing the menu to distribute might also do the following:
- if the user-defined commandbar is not enabled users won't see it, neither in menu nor in context menu
- the user-defined control should have a tag so unique that it can be identified precisely
- the Workbook_Open event could look whether the custom menu is already in the desired place (if not, copy it there), hide the user-defined commandbar and close the workbook.

Everything Gavin (thanks again for this idea) proposed would be fulfilled.

Unfortunately I do not know yet how to adapt the users' Excel installations easily.
As it's only one line of code

application.AltStartupPath="your network folder here"

even an experienced user could copy the line from an email and execute it in the direct window.
But to allow him this of course depends on your company and your users.

Regards,

Markus

 

[jjjjjjohn]

Hi Markus and Gavin,

Thanks so much for the information but I'm affraid you've lost me a bit.

Gavin's reference to "custom toolbars" and Markus's reference to "user-defined commandbar" makes me wonder if we are talking about different things. For me a "custom menu" is something like this (

http://spreadsheetpage.com/graphics/tips/menu2.gif).

Can I apply your ideas for this kind of menu?

Thanks again kind regards,
John

 

[Markus4]

Hi jjjjjohn,

yes, you can. Excel (and with it Office) is not really clear about controls. Sub-menus are treated like commandbar buttons. I am suggesting to use this to fulfil your task.

In your case I suggest you just make your own sub-menu on a user-defined commandbar, append it to a workbook, (and now comes Gavin's idea) put this workbook on a network drive, make the users's Excel know that this workbook should be loaded at startup, and make the code in this workbook deliver your custom menu.

That's the idea behind it.

Regards,

Markus

 

[Gavona]

Markus,

Just send them a workbook with a macro in it. One line of code:

Application.AltStartupPath = "C:\Users\Family\Documents"

Could even attach the code to a button...Or use the code to test first to see if the path is currently blank, if not list the files in that folder in a workbook and send it to you so you can devise an alternate strategy.

jjjjohn,
Sorry about the language. Commandbars, include menubars and toolbars, at least in more recent versions of Office



Gavin

 

[jjjjjjohn]

Hi Gavin and Markus,

You guys are great and I really appreciate it. I almost understand what you are saying but part of it still escapes me. The parts about the network drive and changing the startup path are clear to me but...

I suggest you just make your own sub-menu on a user-defined commandbar

Would I make the sub-menu manually or with code?

I first tried making it with code and everything worked great. But I don't see how it is really different from putting the same code directly in the Add-In because it still executes with every startup and does not include the custom icons.

I then tried to make it manually but then I couldn't figure out how to "append" it to a workbook. When I manually make a commandbar (menu or toolbar) they show up in ALL of my Excel workbooks so I'm not sure how I would "append" it to one specific workbook.


Thanks again and Kind Regards,
John

 

[Gavona]

First make your toolbar(commandbar) manually, complete with your customised icons.

In short you attach your toolbar to a workbook (customise, toolbars,attach). When that workbook is opened the custom toolbar will be added provided that no toolbar of the same name already exists.

To expand on the above a little right click in the menu area and select Customise
Then click on the Toolbars tab, select your custom toolbar and then click on Attach.

Even though attached to a workbook it will be visible / available to all workbooks unless you write code to stop it. (What behaviour do you want?)

Having attached toolbar to the workbook, save the workbook.
Delete the toolbar from excel (customise, toolbars,.... delete)
close excel.
Re-open Excel - no custom toolbar
Open your workbook - custom toolbar appears*
Close Excel
Re-open Excel and the custom toolbar is there, (added to excel.xlb)

*if the custom tollbar does not appear then you did not properly delete it a few steps earlier.



Gavin

 

[Markus4]

Have a star, Gavin, for providing quick and precise answers.

Markus

 

[Gavona]

Appreciated Marcus. Mind you from the time stamps they don't appear to be quick - must be different time zones I guess!

Gavin

 

[jjjjjjohn]

Hi Gavin and Markus,

Now I got it. I made the custom toolbar, attached it, deleted it and got it to show up again by opening the original workbook.

But how do I apply this concept to a sub-menu like this (

http://spreadsheetpage.com/graphics/tips/menu2.gif)?

There doesn't seem to be an "append" option for sub-menus.

Thanks again for all the help!
John

 

[Markus4]

HI jjjjjohn,

just create your sub-menu 'Budget Tools' on a custom toolbar.

MS Office is not really clear about menus and controls. That is why

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

will do what you want: add your 'Budget Tools' sub-menu to your user's worksheet menu bar.

Regards,

Markus
_______________
P.S.: Maybe one of the professionals could post a few lines here on how VBA treats controls and menus. In the past that was a riddle to me. I have become used to using them, but honestly, I cannot say I really understand how they work.

 

[jjjjjjohn]

Hi Gavin and Markus,

Great! That works. The only thing I would want to change would be to have the custom toolbar control added to a specific control (either custom or standard) on the "Worksheet Menu Bar" Command Bar.

I tried the following:

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

but got a 'Type Mismatch' error message.

Any ideas?


Thanks again for all the help!
John

 

[Markus4]

Hi jjjjjohn,

without seeing the target Excel I can only guess:
Asking about the Move method Excel help answers: target must be a valid command bar. So it might be that Excel does not recognize Controls("Custom Menu") in

"Application.CommandBars("Worksheet Menu Bar").Controls("Custom Menu"), Before:=1"

as a valid command bar. How did you generate this control/commandbar?

Regards,

Markus