Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
On 15 Sep, 13:58, Joel 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying ma
It is still the caption
ActiveSheet.CommandButton1.Caption = "Chrisso" "Chrisso" wrote: On 15 Sep, 13:58, Joel 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing the text of a form command button from underlying macro
On 15 Sep, 14:43, Andy Pope 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro attached to command button - VBA form | Excel Discussion (Misc queries) | |||
Accessing the underlying OLE data | Excel Programming | |||
Command Button vs Form Button | Excel Programming | |||
Accessing Active X command button properties | Excel Programming | |||
Command Button vs Form Button | Excel Programming |