Add control to menubar

[Zone]

I want to add a control to Excel's standard menubar to show some
text. The control will not do anything other than show its text.
TIA, James

 

[Bob Phillips]

with application.commandbars("Worksheet Menu Bar")
with .controls.add(name:="myButton",temporary:=True)
.caption = "some text
end with
end with

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Correction.

With Application.CommandBars("Worksheet Menu Bar")
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "some text"
.Style = msoButtonCaption
End With
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Helmut Weber]

Hi Bob

perfect.

But how to add a control button with "some text"
to a commandbar other then menu bar?

Setting the caption results in nothing for:

Sub x()
With Application.CommandBars("standard")
.Controls.Add Type:=msoControlButton
.Controls(.Controls.Count).Caption = "xxx"
End With
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Dave Peterson]

Don't forget to change the style:

..Style = msoButtonCaption

Option Explicit
Sub x()

Dim myCtrl As CommandBarControl

With Application.CommandBars("standard")
Set myCtrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With myCtrl
.Caption = "xxx"
.Style = msoButtonCaption
.OnAction = "'" & ThisWorkbook.Name & "'!macro1"
End With

Set myCtrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With myCtrl
.Caption = "yyy"
.Style = msoButtonCaption
.OnAction = "'" & ThisWorkbook.Name & "'!macro2"
End With
End With
End Sub
Sub macro1()
MsgBox "macro1"
End Sub
Sub macro2()
MsgBox "macro2"
End Sub

 

[Zone]

Thank you, Bob. Works perfect! James

Correction.

    With Application.CommandBars("Worksheet Menu Bar")
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .Caption = "some text"
            .Style = msoButtonCaption
        End With
    End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -