Solved: Can the Text in a Form Control Button Be Read into VBA?

[ronjon65]

I need to use a form control button (not an activeX control). I would like to output the text in the button to a cell. Can this be done?

[Bob Phillips]

Is it on the worksheet? Do you know its name?

[ronjon65]

Yes, it is on a worksheet.

Not sure I understand the second part of your question, but I can explain the intent.

A button is named "Test". A macro is assigned to this button and some code would ideally write "Test" into a cell. Subsequent code then uses this phrase.

I can create a macro for each button, which links the text to the VBA (manually). However, I have hundreds of these buttons and it will mean a lot of work and book keeping (and extra code). Instead, I want to use the same code and the only difference comes from the text written in the button. This will save a tremendous amount of time and reduce chance of error.

[GTO]

Presuming you mean using the button's Caption for the text, you could try something like:

In a Junk Copy of your workbook:

In a Standard Module:

Option Explicit

Sub Button_Click(ByVal sText As String)

ActiveSheet.Range("A1").Value = sText

End Sub

Sub BuildOnAction()
Dim btn As Excel.Button
Dim sOnAction As String
Dim bPadded As Boolean

sOnAction = ThisWorkbook.Name
If InStr(1, sOnAction, Chr(32)) > 0 Then sOnAction = "'" & sOnAction & "'"

sOnAction = sOnAction & "!'Button_Click"

For Each btn In ActiveSheet.Buttons
'Debug.Print ">" & sOnAction & Chr(32) & """" & btn.Caption & """'" & "<"
btn.OnAction = sOnAction & Chr(32) & """" & btn.Caption & """'"
Next
End Sub

Ensure you have the correct sheet selection before running, and of course, change the name of the macro where appropriate.

Hope that helps,

Mark

[Aflatoon]

Sub Button_Click(ByVal sText As String)

ActiveSheet.Range("A1").Value = activesheet.buttons(application.caller).Caption

End Sub

and assign that macro to all your buttons.

[ronjon65]

Aflatoon, that does work well.

But I changed gears a bit now and am using text boxes to assign a macro now (as opposed to buttons). Any ideas how change the code to do that?

[Aflatoon]

Did you try:
activesheet.textboxes(application.caller).text

[ronjon65]

Well, no...because I was not aware, haha.

But thanks, that works great! Saves me a ton of time and mess creating unique macros.