Add A Command Button To The Menu Bar

[Minitman]

Greetings,

I need to add a command button to the menu bar.

How is that done?

-Minitman

 

[JulieD]

Hi

choose view / toolbars / customize
choose the commands tab
in the categories section choose Macros
then you have the option of a Custom Menu Item or a Custom Button
drag to where you want it

to do anything to it, leave the customize dialog box open, and right mouse
click on custom menu item or custom button ... lots of settings to play with

hope this helps
Cheers
JulieD

 

[Ron de Bruin]

Hi Minitman

Manual or with code?

 

[Minitman]

Hey Julie,

Thanks, that is what I was looking for/

-Minitman

 

[Minitman]

Hey Julie,

Just a quick questions, how do I remove it?

-Minitman

 

[JulieD]

hold down ALT and drag if off the toolbar

Hey Julie,

Just a quick questions, how do I remove it?

-Minitman

 

[David]

Ron de Bruin wrote

Manual or with code?

I would like to know how to do it with code. My attempts always showed a
blank button underneath it when clicked.

 

[Minitman]

Cool - Thanks

-Minitman

hold down ALT and drag if off the toolbar

 

[David]

David wrote

I would like to know how to do it with code. My attempts always showed a
blank button underneath it when clicked.

Got it worked:
Set MyCommandBar = Application.CommandBars(1)
Set Item = MyCommandBar.Controls.Add(Type:=msoControlButton)
Item.Caption = "New Month"
Item.Style = msoButtonCaption
Item.OnAction = "New_Month"

Key was Item.Style

 

[Ron de Bruin]

Hi David

Here is a example to add and delete a item on the menu bar

Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
With Application.CommandBars(1)
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = "&Hi"
.Controls("Hi").OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub

 

[David]

Ron de Bruin wrote

Here is a example to add and delete a item on the menu bar

Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
With Application.CommandBars(1)
.Controls.Add(Type:=msoControlPopup, before:=1).Caption = "&Hi"
.Controls("Hi").OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub

Hi, Ron

Thanks for the example. I tried to utilize this technique via
Workbook_Activate() and Workbook_Deactivate() Subs as I do in other
workbooks, and it bombed, not being able to find the macro

I then tried a right-click|Customize, right-click the item|Assign Macro
and it ran the macro, but with the aformentioned blank button appearing
underneath the added menubar item

I never tried to programmatically add a ControlButton to the Menu Bar
before, and it works a little differently, it seems, than a ControlPopup

The method I posted works as desired, with only a minor cosmetic
nuisance: The button itself is a tad taller than the other menu items, so
the menu bar expands slightly in height. If I could find a solution to
that, I would be a happy camper

FWIW, I'm running XL2000

 

[Dave Peterson]

I put this under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Activate()
Call MenuBar_Item
End Sub
Private Sub Workbook_Deactivate()
Call MenuBar_Item_Delete
End Sub


And all of Ron's code in a General module and it worked nicely for me.

(I'm betting you didn't put the TestMacro in a General Module.)

======
When I tried your code, the control matched nicely (xl2002). I don't have a
suggestion for you.

 

[David]

Dave Peterson wrote

I put this under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Activate()
Call MenuBar_Item
End Sub
Private Sub Workbook_Deactivate()
Call MenuBar_Item_Delete
End Sub


And all of Ron's code in a General module and it worked nicely for me.

(I'm betting you didn't put the TestMacro in a General Module.)

======
When I tried your code, the control matched nicely (xl2002). I don't
have a suggestion for you.

Hi, Dave

Your methods work fine here, too, and so did Ron's which got me excited. It
was when I tried to modify my existing routine that I ran into trouble, and
I'm trying to avoid a major rewrite to meet my goal. My existing routine
continues on to add a menu item with submenu items.

Concerning ControlButton I currently use, height of menu bar and others
(Formatting, Standard) differ here and my method results in a button that
matches the height of those.

I'm at a loss to explain why things happen differently here than they do
for you, but I'm going to keep tinkering. Thanks for the input.

 

[David]

Hi, Dave (or Ron)

As long as the routines you offer show a MsgBox, things work as advertized.
It is when I use the control to call a Sub that actually does some work
that the new menu item remains depressed and an empty what I'll call
submenu appears underneath it. If I stick a MsgBox at the beginning of that
Sub, the button doesn't stay depressed and the empty submenu doesn't appear
and the remaining code executes fine after OK'ing the MsgBox.

 

[David]

Dave or Ron,

Try making Sub TestMacro() do something simple like:
Sub TestMacro()
Range("A1").Select
End Sub

What happens then?

 

[Ron de Bruin]

Hi David

This is a normal behavior for a item on the menu bar.
Normally you always use Sub menu's under a item on the Menu bar.

Use a sub menu which call the macro to avoid this

 

[Dave Peterson]

Ah, I didn't get it.

A minor modification to Ron's routine:

Option Explicit
Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("&Hi").Delete
On Error GoTo 0
With Application.CommandBars(1)
With .Controls.Add(Type:=msoControlButton, Before:=1)
.Style = msoButtonCaption
.Caption = "&Hi"
.OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("&Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
ActiveSheet.Range("A1").Select
'just to notice something changed
'MsgBox ActiveCell.Address
End Sub

A popup does something different than a popup.

 

[Dave Peterson]

Actually:
A popup does something different than a popup.
isn't true!

But "a popup does something different than a button" is!

(That sentence was longer than 3 words and I lost my train (small as it is) of
thought!)

 

[Ron de Bruin]

Good change Dave

 

[Dave Peterson]

A little teamwork sometimes makes things right (or even just close enough!
<vbg>).

Good change Dave