Menu bar Macros

[pcor]

I have 4 macros that I use frequently. I have attached them to my menu bar
How do I copy/do something so that I get the same menu bar on a DIFFERENT
computer
Thanks

--
Norton Virus checker 2003 says this email is clean

[Bob Phillips]

If you change your approach slightly to running a workbook that creates the
menu dynamically, you can pass that file around. If your colleagues put it
in the XLStart directory, it will be automatically loaded.

This is an example that I posted recently of the sort of code you need. This
is for a toolbar, but a menubar is very similar.

You can have a workbook that create a toolbar dynamically. This example adds
an item to the formatting toolbar.

Dim oCtl As CommandBarControl
With Application.CommandBars("Formatting")
Set oCtl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl
.BeginGroup = True
.Caption = "myButton1"
.OnAction = "myMacro"
.FaceId = 27
End With
End With

You can add to any toolbar, or even create your own. like so

Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Set oCB = Application.CommandBars.Add(Name:="myCB", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl
.BeginGroup = True
.Caption = "myButton1"
.OnAction = "myMacro"
.FaceId = 27
End With
.Visible = True
.Position = msoBarTop
End With

Or you can be a lot more extravagant.

As I alway say with this, I also suggest you check out John Walkenbach's
site at http://j-walk.com/ss/excel/tips/tip67.htm to help find the values of
the FaceIds, which will give you a decent toolbar button image.

--

HTH

Bob Phillips

"pcor" <ianm140...@rogers.com> wrote in message
news:xdeTa.4223$Ii1....@news02.bloor.is.net.cable.rogers.com...

[pcor]

Thanks a lot BOB BUT I have a small problem.....I am new at this
Where do I place that code?
How do I action it.
Thanks

"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message
news:utmD8aIU...@tk2msftngp13.phx.gbl...

[Bob Phillips]

If you are that unsure, it is probably best that you detail your menu
items, the hierarchy, the caption, which menu they hang off (or if it's a
new one, the caption for that) the macros that each menu option (and
anything you can think of that I may have missed), and I will; create the
proper code for you and tell you how to install it.

Regards

Bob

"pcor" <ianm140...@rogers.com> wrote in message

news:bMgTa.5869$Ii1....@news02.bloor.is.net.cable.rogers.com...

[pcor]

My most sincere thanks

The tool bar is called DIP
on that tool bar there are four button that run four macros
1. saveenv
2, savemyprog
3. macro4
4. dater

all the macros are located in mymacros.xls
Again much appreciated.
I am sure that once I see your code I will be able to duplicate it later.
Thanks
Ian

"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message

news:OU7GPIJU...@TK2MSFTNGP12.phx.gbl...

[Bob Phillips]

I am not sure now whether it is a menubar or a toolbar as you have said
both, so I will assume toolbar and work from there.

This is the code

Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("DIP").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="DIP", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)

With oCtl
.BeginGroup = True

.Caption = "savenv"
.OnAction = "savenv"

.FaceId = 27
End With

Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30

End With
.Visible = True
.Position = msoBarTop
End With

I would add this to the mymacro.xls workbook, and put it the worrkbook open
event. To do this, follow these steps
- goto into the VB IDE
- in the explorer pane on the left, select the 'mymacro.xls' workbook
- double-click the 'ThisWorkbook' class module under the 'Microsoft Excel
Objects' folder (may need to to click a plus sign beside Microsoft Excel
Objects to expoand it)
- a code window should open, and in the 'General' dropdown, select Workbook
- this should create a Workbook_Open event, paste the above code into that.

Save your file, and hopefully that will do it.

I restate that you should check the FaceIds at John Walkenbach's site to get
meaningful icons.

HTH

Bob Phillips

"pcor" <ianm140...@rogers.com> wrote in message

news:9HhTa.6455$Ii1....@news02.bloor.is.net.cable.rogers.com...

[pcor]

THANKS from Canada
Works great
I already had the FaceIds.xla
I changed a few of the icon # and and it looks great and works fine
Many thanks

Ian
"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message

news:u7ZrQpJU...@TK2MSFTNGP11.phx.gbl...