Hi all.
I have been making a series of "autofill" type macros in excel with buttons
in my tool bar.
While they're working great, today it dawned on me that I should be able to
have fewer items if I were to have a drop down menu as one of my tool bars.
This would allow me to have autofill names in the drop down, instead of
having to have 25 or so buttons across the top of my worksheet on top of all
the other single click icon/buttons.
I have found, and see where I can place drop downs within the worksheet
itself, but haven't yet figured out how to get it to work for the tool bar up
top.
1- is it possible to have a drop down menu on the excel tool bar? Not in the
worksheet....
If so, how do I make it?
If I'm in the wrong place, please move this post over to the right spot, and
let me know.

Thanks.

hi,
I think you are talking about a "new Menu".
Right click the tool bar you want the new menu on.
from the options list, select customize at the bottom.
from the cumtomize box, left click the commands tab.
on the left side, scroll down to the bottom and select "new menu"
on the right side, left click-hold-drag new menu to the tool bar you want it
on.
default name = new menu
right click new menu. here you can give it a personal name
note: while the customize box is open, you are in edit mode.
begin dragging your icons to the new menu. right click each. you can have
icon only, icon and text or text only. pick one.
make sure your macros followed the drag opertions. (I'm foggy on that part).
if not the right click each menu item and re-assign macros.
when done, close the customize box.

regards
FSt1

"SteveDB1" wrote:

Hi all.
I have been making a series of "autofill" type macros in excel with buttons
in my tool bar.
While they're working great, today it dawned on me that I should be able to
have fewer items if I were to have a drop down menu as one of my tool bars.
This would allow me to have autofill names in the drop down, instead of
having to have 25 or so buttons across the top of my worksheet on top of all
the other single click icon/buttons.
I have found, and see where I can place drop downs within the worksheet
itself, but haven't yet figured out how to get it to work for the tool bar up
top.
1- is it possible to have a drop down menu on the excel tool bar? Not in the
worksheet....
If so, how do I make it?
If I'm in the wrong place, please move this post over to the right spot, and
let me know.

Thanks.

In case you were looking for a programmatic way I append the following
example code. Note that if others are using your program, if they do a "Save
As..." then the menu items will be reassigned to the macros in the new wb
instead (i.e. will point to the macros in the new wb). Running the macros
from the original will cause the new wb to open or will raise an error if it
cannot be found. This can be a real pain.

To avoid the above, I suggest that you use the wb_open event to call the
MakeMenu macro and recreate the menu each time. This will always ensure that
the macros assignments point to your wb. The menu is made Temporary so that
it is destroyed each time the wb is closed.


Sub MakeMenu()
Dim i As Integer
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

arr1 = Array("Macro1", "Macro2", "Macro3", "Macro4", "Macro5")
arr2 = Array("Caption1", "Caption2", "Caption3", "Caption4", "Caption5")
arr3 = Array(132, 133, 134, 135, 136)
With Application.CommandBars(1).Controls.Add(msoControl Popup, Temporary:=True)
.Caption = "&My macro list"
.TooltipText = "Select a macro from the list"
For i = 0 To 4
With .Controls.Add
.OnAction = arr1(i)
.Caption = arr2(i)
.FaceId = arr3(i)
End With
Next
End With
End Sub

Regards,
Greg

Option Explicit

Sub CreatePopupToolbar()

Dim TBar As CommandBar
Dim btnNew As CommandBarButton
Dim btnPop As CommandBarPopup

' Add a new Toolbar
Set TBar = CommandBars.Add(Name:="Toolbar Name")
TBar.Visible = True

' Add a dropdown button
Set btnPop = TBar.Controls.Add(Type:=msoControlPopup)
btnPop.Caption = "Dropdown Button Caption"

' Add standard buttons to the "Dropdown Button"
Set btnNew = btnPop.Controls.Add(Type:=msoControlButton)
btnNew.Style = msoButtonIconAndCaption
btnNew.Caption = "Caption"
btnNew.FaceId = 84 ' This is E but can be changed or removed.
btnNew.OnAction = "MacroName"

Set TBar = Nothing
Set btnPop = Nothing
Set btnNew = Nothing

End Sub

Copy the last two paragraphs to create a new dropdown list, copy only the
last paragraph to create another button within a dropdown. (Changing names
of course.)

Alan


"SteveDB1" wrote in message
...
Hi all.
I have been making a series of "autofill" type macros in excel with
buttons
in my tool bar.
While they're working great, today it dawned on me that I should be able
to
have fewer items if I were to have a drop down menu as one of my tool
bars.
This would allow me to have autofill names in the drop down, instead of
having to have 25 or so buttons across the top of my worksheet on top of
all
the other single click icon/buttons.
I have found, and see where I can place drop downs within the worksheet
itself, but haven't yet figured out how to get it to work for the tool bar
up
top.
1- is it possible to have a drop down menu on the excel tool bar? Not in
the
worksheet....
If so, how do I make it?
If I'm in the wrong place, please move this post over to the right spot,
and
let me know.

Thanks.

Ok guys.
I'm going to tinker with each of these, and I'll get back to you to let you
know how it went, and if I need further clarification.
Thanks, each of you.

Hi Alan,

Ok, from what I can understand of what I've done, yours is the one that I'm
looking for.
Now for some questions.
When you say copy the first paragraph for just the drop down, please
highlight that, so I'm not misreading it, adding too little, or too much--
I'm notorious for doing something stupid, and spending hours afterwards to
figure out why.
The same for a new button-- although, using the "customize menu" option
seems to be working just as easily-- I'd still like to understand what's
happening.

Where can I read more on this specific topic? Details.....

I'll be making about 7 or 8 of these (for me) drop downs for excel at work,
and need to understand it for both explaining to others, and repeatability.
Again, thank you for your help.

Greg, yours is just a bit too far beyond my comprehension. I did tinker with
it, but didn't seem to get what's going on. And none of the changes that I
made appeared as anticipated. Although, I'll be the first to admit that I
just plain have no clue of what I'm doing, and am trying to figure it out as
I go. Arrays I get, but my limits extend to 1 semester of C++ at school, and
I only got a C in that.
Thank you.


"Alan" wrote:

Option Explicit

Sub CreatePopupToolbar()

Dim TBar As CommandBar
Dim btnNew As CommandBarButton
Dim btnPop As CommandBarPopup

' Add a new Toolbar
Set TBar = CommandBars.Add(Name:="Toolbar Name")
TBar.Visible = True

' Add a dropdown button
Set btnPop = TBar.Controls.Add(Type:=msoControlPopup)
btnPop.Caption = "Dropdown Button Caption"

' Add standard buttons to the "Dropdown Button"
Set btnNew = btnPop.Controls.Add(Type:=msoControlButton)
btnNew.Style = msoButtonIconAndCaption
btnNew.Caption = "Caption"
btnNew.FaceId = 84 ' This is E but can be changed or removed.
btnNew.OnAction = "MacroName"

Set TBar = Nothing
Set btnPop = Nothing
Set btnNew = Nothing

End Sub

Copy the last two paragraphs to create a new dropdown list, copy only the
last paragraph to create another button within a dropdown. (Changing names
of course.)

Alan


"SteveDB1" wrote in message
...
Hi all.
I have been making a series of "autofill" type macros in excel with
buttons
in my tool bar.
While they're working great, today it dawned on me that I should be able
to
have fewer items if I were to have a drop down menu as one of my tool
bars.
This would allow me to have autofill names in the drop down, instead of
having to have 25 or so buttons across the top of my worksheet on top of
all
the other single click icon/buttons.
I have found, and see where I can place drop downs within the worksheet
itself, but haven't yet figured out how to get it to work for the tool bar
up
top.
1- is it possible to have a drop down menu on the excel tool bar? Not in
the
worksheet....
If so, how do I make it?
If I'm in the wrong place, please move this post over to the right spot,
and
let me know.

Thanks.

Hi Greg.
After spending more time away, and being able to think about what your
routine does, I think that I'm understanding it further.
arr1 is the macro names. You've set it below to .OnAction
arr2 is the names on the menu to activate the respective macros-- button
names. This is set to .Caption.
arr3 is the image/icons along the left side of the drop-down menu. Set to
..FaceId.
Cool feature, but not important for my purposes. Nice to know though. How
many image choices are there? got a list with icons/numbers to have for
future purposes?
Each of these arrays can be expanded to include any, and all macros that I
prefer to have under the "header name" of choice. That seems clear.
What I don't understand is:
1- why temporary is set to true.
Does this turn the menu off each time I close the workbook that I'm working
on? And I need to restart it-- it appears that all of the contents would
remain intact regardless of being turned off or not?
2- why it comes up at the top-- where file, edit, view, format, etc... are
located-- instead of a menu that can be turned on, and off in the icon menu--
where I had wanted it to be. What would I need to change to make it be down
below, instead of at the top where file, edit, view, etc... are located?
3- what would occur if I were to add a 4th, 5th, ..., nth array?
I get that I'd need to add corresponding declarations, etc... Would there be
any purpose to them that I could make use of? Thinking about it makes me
think they'd be superfluous.
I hope my questions make sense. If not, let me know.
Thanks.





"Greg Wilson" wrote:

In case you were looking for a programmatic way I append the following
example code. Note that if others are using your program, if they do a "Save
As..." then the menu items will be reassigned to the macros in the new wb
instead (i.e. will point to the macros in the new wb). Running the macros
from the original will cause the new wb to open or will raise an error if it
cannot be found. This can be a real pain.

To avoid the above, I suggest that you use the wb_open event to call the
MakeMenu macro and recreate the menu each time. This will always ensure that
the macros assignments point to your wb. The menu is made Temporary so that
it is destroyed each time the wb is closed.


Sub MakeMenu()
Dim i As Integer
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

arr1 = Array("Macro1", "Macro2", "Macro3", "Macro4", "Macro5")
arr2 = Array("Caption1", "Caption2", "Caption3", "Caption4", "Caption5")
arr3 = Array(132, 133, 134, 135, 136)
With Application.CommandBars(1).Controls.Add(msoControl Popup, Temporary:=True)
.Caption = "&My macro list"
.TooltipText = "Select a macro from the list"
For i = 0 To 4
With .Controls.Add
.OnAction = arr1(i)
.Caption = arr2(i)
.FaceId = arr3(i)
End With
Next
End With
End Sub

Regards,
Greg

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

Hello again.
Ok, I finally have a working model of Alan's routine.
Greg, while I like yours, I'd like to take it out of the upper tool bar, and
place it in the lower command bars.
I will however introduce both of them to my colleagues.
Thank you both of you.
And FST1, thanks for trying. I do appreciate it.
I needed an actual drop down on my icon-based toolbars.
Best to all.



"SteveDB1" wrote:

Hi all.
I have been making a series of "autofill" type macros in excel with buttons
in my tool bar.
While they're working great, today it dawned on me that I should be able to
have fewer items if I were to have a drop down menu as one of my tool bars.
This would allow me to have autofill names in the drop down, instead of
having to have 25 or so buttons across the top of my worksheet on top of all
the other single click icon/buttons.
I have found, and see where I can place drop downs within the worksheet
itself, but haven't yet figured out how to get it to work for the tool bar up
top.
1- is it possible to have a drop down menu on the excel tool bar? Not in the
worksheet....
If so, how do I make it?
If I'm in the wrong place, please move this post over to the right spot, and
let me know.

Thanks.

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

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