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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:ej1Hv1%(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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" <bob....@somewhere.com> 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" <bob....@somewhere.com> wrote in message
>
> news:ej1Hv1%(E-Mail Removed)...
>
>
>
>
>
> > * *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" <jkend69...@aol.com> wrote in message
> >news:(E-Mail Removed)...
> >>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 -