PDA

View Full Version : Code to add a menu, macros to it and remove when book closes



dmkitz
05-22-2007, 02:16 PM
Can someone please point me to a good discussion of how to write code to: (1) create a custom menu; (2) add macros to it; and (3) remove the custom menu when the workbook closes?

I'm working in Excel 2000. I've created a workbook that contains macros on a custom toolbar. My problem is that they are not executing correctly when the workbook is Saved as a different filename. The toolbar buttons open the original workbook and run the macros from it. This is not good.

Someone suggested the remedy in my first paragraph, and I'd like to do some research. Thanks.

Bob Phillips
05-22-2007, 02:31 PM
If you put code in the appropriate workbook open event, and
delete it in the close it will exist only for that workbook.

Here is an example of a building a commandbar on the fly
when you open a workbook. It adds a sub-menu to the Tools menu.



Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myButton"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub


To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

johnske
05-22-2007, 03:22 PM
Can someone please point me to a good discussion of how to write code to: (1) create a custom menu; (2) add macros to it; and (3) remove the custom menu when the workbook closes?

I'm working in Excel 2000. I've created a workbook that contains macros on a custom toolbar. My problem is that they are not executing correctly when the workbook is Saved as a different filename. The toolbar buttons open the original workbook and run the macros from it. This is not good.

Someone suggested the remedy in my first paragraph, and I'd like to do some research. Thanks.Yes, the remedy is in your 1st paragraph. Whenever you make any changes to the user interface (UI) of a workbook (such as adding a toolbar) all such changes should always be reversed when the workbook is closed to prevent such issues arising. XLD has given you an example above of how to do this...

On the other hand, if you want those changes to apply to all workbooks that are opened, then the code for this should either be in your Personal.xls, some other workbook in the XLSTART folder, or in an add-in. :)

daft009
06-03-2007, 02:30 PM
i'm new to VBA... so could you please tell me how to change the above code so instead of buttons I have a drop down menu.

I'm having the same problem as the OP.

my goal is the same:

menu with A,B & C.
under which there will be A1,A2, B1,B2, C1,C2 etc.
each of them needs to run a macro.

right now, changes made to the toolbar don't carry over to another pc running excel... its got me stumped.

Bob Phillips
06-03-2007, 02:32 PM
i'm new to VBA... so could you please tell me how to change the above code so instead of buttons I have a drop down menu.

I'm having the same problem as the OP.

my goal is the same:

menu with A,B & C.
under which there will be A1,A2, B1,B2, C1,C2 etc.
each of them needs to run a macro.

right now, changes made to the toolbar don't carry over to another pc running excel... its got me stumped. You start by saying a dropdown, then you describe buttons. Which do you want?

daft009
06-03-2007, 04:14 PM
hmm.. sorry, i mean a drop down menu like 'file' 'edit' etc

i created it via, tools>customise>commands>rearrange commands

lucas
06-03-2007, 06:31 PM
I am lazy so I use this (http://j-walk.com/ss/excel/tips/tip53.htm)
easy to configure and maintain.

You should try to understand what Bob and John are saying first.
1 build it when the workbook opens
2 delete it when the workbook closes

daft009
06-03-2007, 06:47 PM
i tried that one, but I didn't like how it added it to the existing menu bar (next to 'file' 'edit' etc)

i wanted it to be a new toolbar which user can show/hide if needed/not needed.

lucas
06-03-2007, 07:49 PM
This one has buttons (http://vbaexpress.com/kb/getarticle.php?kb_id=921)

daft009
06-03-2007, 08:54 PM
but they are buttons?

i'm looking for a drop down menu bar kinda thing...
link this pic..

img171.imageshack.us/img171/3387/menuso4.jpg

Bob Phillips
06-04-2007, 12:47 AM
The example that I gave earlier does just that,

That is not a dropdown, but a simple menu with submenus/items.

lucas
06-04-2007, 07:04 AM
The main purpose of this site is to provide assistance to those learning VBA or require assistance with their coding, not to provide full solutions for free. If you can post your modifications with a note of your problems, we'll try to assist.
And I will add to what Bob has said....you have been given more than enough information in this thread to do what you ask.....at this point you would just need to combine the information that Bob posted in post#2 with the file I posted in post#9 to achieve what you want.

I will add that if that task is daunting to you then you may be in over your head trying to do this....You will have to understand how this works to be able to create the menu that you want and have it run the macro's that you wish to run.

Try to figure this out and if you have specific questions then post them here for more help. We will be more than glad to assist.

attached is an example that I got from an old Word document that I have. Read the comments to see how it works.

daft009
06-07-2007, 03:52 AM
well thanks fellas
its amazing what one can do when one isn't overworked and stressed.. :p

managed to finally get some quiet time to do this and it all works :D

now my next challenge will be to code a drop down box in a macro :)