Accessing the text of a form command button from underlying macro

[Chrisso]

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

 

[Joel]

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.

 

[Joel]

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]

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.













- 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

 

[Andy Pope]

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

 

[Joel]

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

 

[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












- Show quoted text -

Thanks Andy - that will do nicely. Chrisso