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

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)



"Zone" wrote in message
oups.com...
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

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)

"Bob Phillips" wrote in message
...

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)



"Zone" wrote in message
oups.com...
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

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"

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

Helmut Weber wrote:

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

Thank you, Bob. Works perfect! James

On Mar 31, 7:45�pm, "Bob Phillips" wrote:
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)

"Bob Phillips" wrote in message

...





* *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)

"Zone" wrote in message
roups.com...
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- Hide quoted text -

- Show quoted text -