Hi All

I have a Forms command control that my users use to toggle the
visiblity of my legend/key. At the moment my command button's text is
"Toggle Legend" which is exactly what the macro behind it does.

Unfortunately some of my users do not know what toggle means and are
confused. I therefore would like to be able to change the text of my
command button each time it is clicked - from "Show Legend" to "Hide
Legend".

However I cannot see how do do this through the object model.

I can get a handle on the shape:
Dim aShape As Shape
Set aShape = ActiveSheet.Shapes(Application.Caller)

But cannot see how to access the text that lives on the button.

Are form command buttons considering shapes? or some other object?

Any ideas?

Thanks in advance,
Chrisso

Open the Command control tool Bar and enter Design mode by clicking on the
Triangle Icon. Then right click the button and select Format Control. You
should be able to change the name.

If you were using a VBA Form, then go to View menu and select Properties
window. then select with mouse the button. Change the caption option for
the button in the properties window.

"Chrisso" wrote:

> Hi All
>
> I have a Forms command control that my users use to toggle the
> visiblity of my legend/key. At the moment my command button's text is
> "Toggle Legend" which is exactly what the macro behind it does.
>
> Unfortunately some of my users do not know what toggle means and are
> confused. I therefore would like to be able to change the text of my
> command button each time it is clicked - from "Show Legend" to "Hide
> Legend".
>
> However I cannot see how do do this through the object model.
>
> I can get a handle on the shape:
> Dim aShape As Shape
> Set aShape = ActiveSheet.Shapes(Application.Caller)
>
> But cannot see how to access the text that lives on the button.
>
> Are form command buttons considering shapes? or some other object?
>
> Any ideas?
>
> Thanks in advance,
> Chrisso
>
>

The instruction I gave last time only works when the button isd created.
Instead
Left click and then right click the button. Select Properties and change
the caption.
"Chrisso" wrote:

> Hi All
>
> I have a Forms command control that my users use to toggle the
> visiblity of my legend/key. At the moment my command button's text is
> "Toggle Legend" which is exactly what the macro behind it does.
>
> Unfortunately some of my users do not know what toggle means and are
> confused. I therefore would like to be able to change the text of my
> command button each time it is clicked - from "Show Legend" to "Hide
> Legend".
>
> However I cannot see how do do this through the object model.
>
> I can get a handle on the shape:
> Dim aShape As Shape
> Set aShape = ActiveSheet.Shapes(Application.Caller)
>
> But cannot see how to access the text that lives on the button.
>
> Are form command buttons considering shapes? or some other object?
>
> Any ideas?
>
> Thanks in advance,
> Chrisso
>
>

On 15 Sep, 13:58, Joel <J...@discussions.microsoft.com> wrote:
> Open the Command control tool Bar and enter Design mode by clicking on the
> Triangle Icon. Then right click the button and select Format Control. You
> should be able to change the name.
>
> If you were using a VBA Form, then go to View menu and select Properties
> window. then select with mouse the button. Change the caption option for
> the button in the properties window.
>
>
>
> "Chrisso" wrote:
> > Hi All
>
> > I have a Forms command control that my users use to toggle the
> > visiblity of my legend/key. At the moment my command button's text is
> > "Toggle Legend" which is exactly what the macro behind it does.
>
> > Unfortunately some of my users do not know what toggle means and are
> > confused. I therefore would like to be able to change the text of my
> > command button each time it is clicked - from "Show Legend" to "Hide
> > Legend".
>
> > However I cannot see how do do this through the object model.
>
> > I can get a handle on the shape:
> > Dim aShape As Shape
> > Set aShape = ActiveSheet.Shapes(Application.Caller)
>
> > But cannot see how to access the text that lives on the button.
>
> > Are form command buttons considering shapes? or some other object?
>
> > Any ideas?
>
> > Thanks in advance,
> > Chrisso- Hide quoted text -
>
> - Show quoted text -

Sorry Joel - I was not clear in my message.

I want to change the text on my button from VBA each time it is
clicked. I know how to change it manually.

Chrisso

Hi,

Try this,

Sub ToggleLegend()

Dim shpTemp As Shape

With ActiveSheet.Shapes(Application.Caller)
If .TextFrame.Characters.Text = "Show Legend" Then
.TextFrame.Characters.Text = "Hide Legend"

' other code

Else
.TextFrame.Characters.Text = "Show Legend"

' other code

End If
End With

End Sub

Cheers
Andy

Chrisso wrote:
> Hi All
>
> I have a Forms command control that my users use to toggle the
> visiblity of my legend/key. At the moment my command button's text is
> "Toggle Legend" which is exactly what the macro behind it does.
>
> Unfortunately some of my users do not know what toggle means and are
> confused. I therefore would like to be able to change the text of my
> command button each time it is clicked - from "Show Legend" to "Hide
> Legend".
>
> However I cannot see how do do this through the object model.
>
> I can get a handle on the shape:
> Dim aShape As Shape
> Set aShape = ActiveSheet.Shapes(Application.Caller)
>
> But cannot see how to access the text that lives on the button.
>
> Are form command buttons considering shapes? or some other object?
>
> Any ideas?
>
> Thanks in advance,
> Chrisso
>

It is still the caption
ActiveSheet.CommandButton1.Caption = "Chrisso"

"Chrisso" wrote:

> On 15 Sep, 13:58, Joel <J...@discussions.microsoft.com> wrote:
> > Open the Command control tool Bar and enter Design mode by clicking on the
> > Triangle Icon. Then right click the button and select Format Control. You
> > should be able to change the name.
> >
> > If you were using a VBA Form, then go to View menu and select Properties
> > window. then select with mouse the button. Change the caption option for
> > the button in the properties window.
> >
> >
> >
> > "Chrisso" wrote:
> > > Hi All
> >
> > > I have a Forms command control that my users use to toggle the
> > > visiblity of my legend/key. At the moment my command button's text is
> > > "Toggle Legend" which is exactly what the macro behind it does.
> >
> > > Unfortunately some of my users do not know what toggle means and are
> > > confused. I therefore would like to be able to change the text of my
> > > command button each time it is clicked - from "Show Legend" to "Hide
> > > Legend".
> >
> > > However I cannot see how do do this through the object model.
> >
> > > I can get a handle on the shape:
> > > Dim aShape As Shape
> > > Set aShape = ActiveSheet.Shapes(Application.Caller)
> >
> > > But cannot see how to access the text that lives on the button.
> >
> > > Are form command buttons considering shapes? or some other object?
> >
> > > Any ideas?
> >
> > > Thanks in advance,
> > > Chrisso- Hide quoted text -
> >
> > - Show quoted text -
>
> Sorry Joel - I was not clear in my message.
>
> I want to change the text on my button from VBA each time it is
> clicked. I know how to change it manually.
>
> Chrisso
>
>

On 15 Sep, 14:43, Andy Pope <a...@andypope.info> wrote:
> Hi,
>
> Try this,
>
> Sub ToggleLegend()
>
> Dim shpTemp As Shape
>
> With ActiveSheet.Shapes(Application.Caller)
> If .TextFrame.Characters.Text = "Show Legend" Then
> .TextFrame.Characters.Text = "Hide Legend"
>
> ' other code
>
> Else
> .TextFrame.Characters.Text = "Show Legend"
>
> ' other code
>
> End If
> End With
>
> End Sub
>
> Cheers
> Andy
>
>
>
> Chrisso wrote:
> > Hi All
>
> > I have a Forms command control that my users use to toggle the
> > visiblity of my legend/key. At the moment my command button's text is
> > "Toggle Legend" which is exactly what the macro behind it does.
>
> > Unfortunately some of my users do not know what toggle means and are
> > confused. I therefore would like to be able to change the text of my
> > command button each time it is clicked - from "Show Legend" to "Hide
> > Legend".
>
> > However I cannot see how do do this through the object model.
>
> > I can get a handle on the shape:
> > Dim aShape As Shape
> > Set aShape = ActiveSheet.Shapes(Application.Caller)
>
> > But cannot see how to access the text that lives on the button.
>
> > Are form command buttons considering shapes? or some other object?
>
> > Any ideas?
>
> > Thanks in advance,
> > Chrisso- Hide quoted text -
>
> - Show quoted text -

Thanks Andy - that will do nicely. Chrisso