What VBA coding is needed to create a button on the main menu bar for a
shortcut to macro.

thanks in advance!

Mike

Dim oCb As CommandBar
Dim oCtl As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "myButton"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "myMacro"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" wrote in message
...
What VBA coding is needed to create a button on the main menu bar for a
shortcut to macro.

thanks in advance!

Mike

Thanks again for the coding.

One other question.

If I were to put this in the Workbook_Open section to have the shortcut
button created when the workbook is opened then what coding would I put in
the Workbook_BeforeClose section to have the button removed?

Mike.


"Bob Phillips" wrote in message
...
Dim oCb As CommandBar
Dim oCtl As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "myButton"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "myMacro"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" wrote in message
...
What VBA coding is needed to create a button on the main menu bar for a
shortcut to macro.

thanks in advance!

Mike

Application.CommandBars("Worksheet Menu Bar").Controls("myButton").Delete

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" wrote in message
...
Thanks again for the coding.

One other question.

If I were to put this in the Workbook_Open section to have the shortcut
button created when the workbook is opened then what coding would I put in
the Workbook_BeforeClose section to have the button removed?

Mike.


"Bob Phillips" wrote in message
...
Dim oCb As CommandBar
Dim oCtl As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "myButton"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "myMacro"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" wrote in message
...
What VBA coding is needed to create a button on the main menu bar for a
shortcut to macro.

thanks in advance!

Mike