Link cell value to the value in a text box

[Andres Benes]

Hi Forum,

I am trying to link a value in a textbox to a specific cell. I.e. value in textbox 1 needs to be displayed in cell A1 sheet 1 for instance. I ahve tried below code :

Sub Copyvalue()
Sheets("Sheet1").Range("A1") = TextBox1.Value

End Sub

But this gives me the error : runtime error 424 - object required. I have also tried below code :

Sub textbox_test1()
Dim strTB As String
strTB = Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text.Value
strTB = Mid(strTB, 3, Len(strTB) - 3) 'Strips unwanted =" and " chars from start/end
Worksheets("Sheet1").range ("A1").Shapes(strTB).TextFrame.Characters.Text
End Sub


But also no luck. What is wrong in above code and what would the correct way to do this action ? I am using excel 2013

 

[Ken Cowen]

If you are using an Active X textbox and the name is TextBox1 then it should work. It does for me. If the code is in a regular code module you will need to identify the sheet as below



Sub Copyvalue()
Sheets("Sheet1").Range("A1") = Sheet1.TextBox1.Value
End Sub

If the code is in the module for the sheet that holds the textbox, then you don't need the sheet1.

If your textbox is on a userform you need to provide a little more information.

What do you use to trigger the Copyvalue code?

Ken

 

[gallen]

If it is an Active X Text Box you can link it to a cell. Right click the textbox in Design mode and select properties.

There is a LinkedCell Property. Set that to the cell you wish to link.

 

[Andres Benes]

If you are using an Active X textbox and the name is TextBox1 then it should work. It does for me. If the code is in a regular code module you will need to identify the sheet as below



Sub Copyvalue()
Sheets("Sheet1").Range("A1") = Sheet1.TextBox1.Value
End Sub

If the code is in the module for the sheet that holds the textbox, then you don't need the sheet1.

If your textbox is on a userform you need to provide a little more information.

What do you use to trigger the Copyvalue code?

Ken

Thanks for your reply.
I have tried both options. Inserted an Active x textbox and via "view code" inputted the code : Sheets("Sheet1").Range("A1") = TextBox1.Value. I get no error, however the value of the textbox does not appear in A1. I edit the value in the active x textbox via right mouse click and then textbox object > edit.

I have also tried to insert a normal text box via insert. I then assign the code via a macro to that textbox. After I have updated the value in the textbox in run the macro by clicking the textbox and then I get the object required error.

What is going wrong here ?

 

[Ken Cowen]

If you want the value of cell A1 to always reflect what is in the textbox then gallen's way should work fine.
Right click on the textbox, in the design mode; then put A1 in linkedCell box. Exit the design mode, and what you type in the textbox will go in A1.
If you want a macro, such as you initially provided, to trigger the moving of info from textbox1 to cell A1, then the slightly modified code I sent you should work; you just need to manually run the code or trigger the code with some other event. You should not have to edit the object to make have your change be reflected in A1.

Good luck

Ken

 

[Andres Benes]

If you want the value of cell A1 to always reflect what is in the textbox then gallen's way should work fine.
Right click on the textbox, in the design mode; then put A1 in linkedCell box. Exit the design mode, and what you type in the textbox will go in A1.
If you want a macro, such as you initially provided, to trigger the moving of info from textbox1 to cell A1, then the slightly modified code I sent you should work; you just need to manually run the code or trigger the code with some other event. You should not have to edit the object to make have your change be reflected in A1.

Good luck

Ken

Thanks it worked !