VBA: how to get text displayed on form control button

[dmash]

Hello,

Does anyone know the VBA syntax used to get the text displayed on a form control button?

This is not the name of the button, which can be retrieved using application.caller, but the text on top of the button itself.

Thanks,
Don.

 

[dmash]

Tinkering around using the macro recording, I found one solution.

ActiveSheet.Shapes(Application.Caller).Select
Range("H2") = Selection.Characters.Text

but I cannot combine it into one command line,
Range("H2") = ActiveSheet.Shapes(Application.Caller).Characters.Text

Anyone knows why?

 

[circledchicken]

Hi,

Try:

Range("H2") = ActiveSheet.Shapes(Application.Caller).AlternativeText

 

[dmash]

Hi,

Try:

Range("H2") = ActiveSheet.Shapes(Application.Caller).AlternativeText

Thank you, that works.

 

[dmash]

Follow up question.

How do I change the display text of a form control button? I'd like to change the display text to the value of cell A2.

I got this from macro recording.

ActiveSheet.Shapes("Button 2").Select
Selection.Formula = "=A2"

Combining that into one liner gives me an error.

ActiveSheet.Shapes("Button 2").Formula = "=A2"

What is the correct syntax?

Thanks.

 

[mikerickson]

AlternativeText is what a web browser sees in some situations. It is intially set to the text of the button, but is not what the user sees when running excel.

To set or read the text on a Forms Button,

With ActiveSheet
    .Shapes("Button 2").TextFrame.Characters.Text = CStr(.Range("A2").Value)
End With

 

[dmash]

AlternativeText is what a web browser sees in some situations. It is intially set to the text of the button, but is not what the user sees when running excel.

To set or read the text on a Forms Button,

With ActiveSheet
    .Shapes("Button 2").TextFrame.Characters.Text = CStr(.Range("A2").Value)
End With

Thank you. Your code works wonderfully for both setting and reading text.