Menu Questions

[David F. Schrader]

Old time Excel user, average macro programmer, "novice" menu
developer. If this is the wrong news group please direct me
to the correct group.

I'm trying to develop a set of menus that will be added to
an individual's Excel screen when they open Excel based on
the (preformatted xxx-yy-div.xls) name of the file. I tried
using the old "tried and true" way of "Record a Macro" to
get a framework. Got just what I wanted until I looked at
the code that was generated (see below for a trimmed down
version).

It didn't include the labels or which "macro's" the various
drop-down options pointed too. I *think* I understand most
of what the program instructions mean, but I where is Excel
storing all of the other information that I entered but is
not listed on these lines?

I know there must be some commands that allow you to set or
change the various elements - name, icon, macro, etc. - but
I haven't found them yet. What I need is a pointer to those.

Second, I need a pointer to where the information is stored
so I know how to disseminate it. (I'd like the first shot to
be via a auto-macro I think, but that may depend on how long
is involved in the actual development of the menu - having
Excel take 10 minutes to start is *way to much* to expect.)

All help appreciated and virtual cases of Oreo's cookies are
awaiting a shipping label for the persons who are able to
help.

Many thanks.

David Schrader

*** "The Record a Macro" results, heavily trimmed ***
Sub Macro3()
Application.CommandBars("Worksheet Menu Bar").Controls.Add _
Type:= msoControlPopup, Before:=13
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=1
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=2
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=3
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=4
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlPopup, Before:=5
Application.CommandBars("Custom Popup 18397804").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=6
Application.CommandBars("Custom Popup 18735269").Controls.Add _
Type:= msoControlButton, ID:=2949, Before:=1
End Sub
*** End ***

 

[Dick Kusleika]

David
[snip]

It didn't include the labels or which "macro's" the various
drop-down options pointed too. I *think* I understand most
of what the program instructions mean, but I where is Excel
storing all of the other information that I entered but is
not listed on these lines?

I know there must be some commands that allow you to set or
change the various elements - name, icon, macro, etc. - but
I haven't found them yet. What I need is a pointer to those.

Second, I need a pointer to where the information is stored
so I know how to disseminate it. (I'd like the first shot to
be via a auto-macro I think, but that may depend on how long
is involved in the actual development of the menu - having
Excel take 10 minutes to start is *way to much* to expect.)

I don't know why, but Excel doesn't record those particular changes. If you
were to rerun your recorded macro, it would not give you what you want.
You'd get the custom buttons added, but with the default captions and no
macros assigned.

In VBA help, look up the CommandBarButton object and take a look at it's
properties. Those are what you will need. My basic construct looks like
this

With Application.CommandBars(1) 'This is Excel's menu
With .Controls.Add(msoControlPopup,,,13) 'Add a new menu item
.Caption = "&MyCaption" 'Caption for the new menu item

With .Controls.Add(msoControlButtton) 'a bunch of With block
creating controls on the new menu
.Caption = "My&FirstItem"
.OnAction = "FirstMacro"
End With
With .Controls.Add(msoControlButton)
.Caption = "My&SecondItem"
.OnAction = "SecondMacro"
End With
'etc for each control on your manu
End With
End With

Creating toolbars on the fly doesn't take long, so you should be fine
putting this in the Workbook_Open event. Make sure you delete the toolbar
when the workbook is closed.

 

[David F. Schrader]

Dick,

Thank you - it had to be something
simple.

{ Virtual Create:
Your Oreo's are virtually attached
and have virtually arrived.
Don't you virtually see them?
Don't they virtually taste good?
}

David

P.S.

{Remember to clean up virtual the Oreo's
when you're finished. dfs }

David
[snip]

It didn't include the labels or which "macro's" the various
drop-down options pointed too. I *think* I understand most
of what the program instructions mean, but I where is Excel
storing all of the other information that I entered but is
not listed on these lines?

I know there must be some commands that allow you to set or
change the various elements - name, icon, macro, etc. - but
I haven't found them yet. What I need is a pointer to those.

Second, I need a pointer to where the information is stored
so I know how to disseminate it. (I'd like the first shot to
be via a auto-macro I think, but that may depend on how long
is involved in the actual development of the menu - having
Excel take 10 minutes to start is *way to much* to expect.)

I don't know why, but Excel doesn't record those particular changes. If you
were to rerun your recorded macro, it would not give you what you want.
You'd get the custom buttons added, but with the default captions and no
macros assigned.

In VBA help, look up the CommandBarButton object and take a look at it's
properties. Those are what you will need. My basic construct looks like
this

With Application.CommandBars(1) 'This is Excel's menu
With .Controls.Add(msoControlPopup,,,13) 'Add a new menu item
.Caption = "&MyCaption" 'Caption for the new menu item

With .Controls.Add(msoControlButtton) 'a bunch of With block
creating controls on the new menu
.Caption = "My&FirstItem"
.OnAction = "FirstMacro"
End With
With .Controls.Add(msoControlButton)
.Caption = "My&SecondItem"
.OnAction = "SecondMacro"
End With
'etc for each control on your manu
End With
End With

Creating toolbars on the fly doesn't take long, so you should be fine
putting this in the Workbook_Open event. Make sure you delete the toolbar
when the workbook is closed.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com