Steve,

..xlb files only store settings for toolbar customizations, not macros. When
you create a toolbar, add a control to an existing one etc., that is where it
all gets recorded. File paths and macro names assigned to customized controls
and buttons are stored there. But the macros themselves remain in the .xls
(or .xla) files. If you do a "Save As..." the record of the file paths gets
changed in the .xlb file so that they now point to the macros in the new wb.

Greg


"SteveDB1" wrote:

Hi Greg.
You are correct, I did state it that way with regard to the placement of my
toolbar. That was my mistake.
I've copied your notes, with my comments into a notepad text file for future
reference. This will allow me to show my colleagues, and they can decide from
there.
The multiple macros we've made, and use regularly will definitely be more
readily accessible, or more organized in the drop-down manner.
Now, you mention an *.xlb file. I'm aware that the macros can be saved in
specific workbooks (wb); thus, as standard practice I've saved all of my
macros in the personal.xls file. Which I thought was strange, as I'd thought
they went into the xla files-- my ignorance showing here.
Best, and again-- thank you.

"Greg Wilson" wrote:

Hi Steve,

Point 1:-
Variables arr1, arr2 and arr3 are used to hold arrays (lists) respectively
of the macro names, button captions and button faceId's (icon graphics).
Imagine if you had 20 macros and wanted to have 20 buttons in the dropdown
menu. Code that individually adds each button and specifies the macro,
caption and faceId properties would get extremely verbose. Using my method,
all you have to do is expand the arrays. Beyond that, all you have to do is
change the line:
For i = 0 To 4 to
For i = 0 To 19

Point 2:-
- Toolbar customizations are stored in a separate file (e.g. Excel.xlb).
They are not part of a particular wb file. Non-temporary toolbar
customizations are accessible when you open a different wb. This at the very
least causes confusion and leaves open the potential for someone to run a
macro on the wrong wb with potential data loss etc.

- If someone decides to make a copy using "Save As..." then the macros
currently assigned to the buttons will be reassigned to those of the new wb.
If you then open the original wb and run a macro, it will cause the new wb to
be opened which is a nuissance. If the new wb cannot be found this will raise
an error and the macros won't work.

- If your program gets put on a network drive, it won't be able to find the
.xlb file; or the .xlb file may get moved, renamed, deleted etc. If you make
a copy of your wb and put it on a different computer, it won't include the
.xlb file.

- If someone monkeys with your toolbar or menu, then you have a problem.

- If you instead programmatically recreate it each time the wb is opened,
then it will always be there and the macro assignments will always point to
your wb. If the .xlb file can't be found then a new one will be created
automatically.

- The "Temporary" parameter causes the toolbar or menu etc. to be
automatically deleted when the application (Excel as opposed to the wb) is
closed. Most people resort to code in the wb's before_close event to do the
deletion but I don't find this necessary. See Note.

Point 3:-
Your post seemed to imply that you wanted the menu on the Worksheet Menu Bar:
< 1- is it possible to have a drop down menu on the excel tool bar? Not in
the
< worksheet....

The line
With Application.CommandBars(1)
adds the new menu to the Worksheet Menu Bar because it is the first in the
toolbar collection (i.e. index number 1). You could change it to refer to
your toolbar by name:
Application.CommandBars("Steve's Toolbar")

Point 4:-
I have my own homemade FaceId (icon) browser. There are 1000's of different
faceId's available. You are welcome to mine but it is probably simpler to
google for a free one. There are a number available.

Note: I find it useful to use the wb_activate and wb_deactivate events to
unhide and hide the toolbar/menu. So if multiple wbs are open they will be
hidden if you switch to a different wb.

Regards,
Greg