CODEWITHSUNDEEP
excelvba
Windmill
Windmill

Reputation: 163

Excel VBA Set a Commandbutton Caption from a Userform Textbox

I have a button that is on an excel spreadsheet (not in a userform) and I have a userfrom that has a textbox on and would like it to, when I enter a name in the textbox on my userform for it to then set the Caption of my button that is on my excel sheet to what ever is entered in the textbox. I would like to know what code I need to use and where to insert that code?

Hope that make sense, please keep in mind I'm a newbie to this so this is why I am asking where to insert the code

Many thanks in advance

Upvotes: 0

Views: 2503

Answers (2)

Rory
Rory

Reputation: 34035

Code in your userform, assuming a textbox named TextBox1, could be like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If Len(Me.Textbox1.text) > 0 then
     ActiveSheet.Buttons("Button 1").Caption = Me.Textbox1.text
   End If
End Sub

or if you want the caption to update as you type:

Private Sub TextBox1_Change()
    If Len(Me.TextBox1.Text) > 0 Then _
        ActiveSheet.Buttons("Button 1").Caption = Me.TextBox1.Text
End Sub

Upvotes: 1

bmgh1985
bmgh1985

Reputation: 789

As you have used "CommandButton" (which is an ActiveX control) yet seemingly heading towards a Form control, I have gone with the 2 methods you will need:

ActiveX:

ActiveSheet.Shapes("YourButton").OLEFormat.Object.Object.Caption = "YourText"

Forms:

ActiveSheet.Shapes("YourButton").TextFrame.Characters.Text = "YourText"

Upvotes: 1

Related Questions