Menu bar Macros

  • Thread starter Thread starter pcor
  • Start date Start date
P

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
 
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.
 
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 said:
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 said:
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
Click to expand...
values
Click to expand...
 
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
 
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
 
Back
Top