How can I make an excel cell equal to the value of a text box

[directionalman]

I am simply attempting to make a cell on the 2nd sheet of a workbook
automatically equal the value of a text box on the 1st sheet that is
used as an input page for the user to input data. None of the data is
used in any calculations. It is simply for header information and
descriptions.

 

[CLR]

This code will get the text from a Text Box to a helper cell on the same
sheet.....then that cell can be linked to whatever sheet you want.....

Sub TextBoxToCell()
Range("a1").Value = [Text box 1].Text
End Sub

hth
Vaya con Dios,
Chuck, CABGx3

 

[Harlan Grove]

CLR wrote...

This code will get the text from a Text Box to a helper cell on the same
sheet.....then that cell can be linked to whatever sheet you want.....

Sub TextBoxToCell()
Range("a1").Value = [Text box 1].Text
End Sub ....

I am simply attempting to make a cell on the 2nd sheet of a workbook
automatically equal the value of a text box on the 1st sheet that is
used as an input page for the user to input data. None of the data is
used in any calculations. It is simply for header information and
descriptions.

It'd be a lot less trouble just to set the Linked Cell property of the
text box to the address of the cell that should contain its text.

Also, the macro above would fail for many reasons. First, spaces are
NEVER allowed in the names of controls. You may believe they add
'readability', but in this case that questionable belief is trumped by
implying functionality that just doesn't exist.

Next, OLE controls are specific to worksheets, so the only way the Text
property above works is when the containing the text box is the active
worksheet when the macro runs, in which case it'd put the .Text into
the *same* worksheet's A1 cell, so not much use when .Text should be
put into a cell in a different worksheet.

Finally, this may be due to an Excel version difference, but Excel 10
(XP) requires [TextBox1].Object.Text rather than [TextBox1].Text.

 

[CLR]

Maybe so Harlan, your language is only a light-year or so beyond mine, so I
don't understnad everything you say.........all's I know is that the code I
posted works in my Excel97 exactly as I stated in my post. I used "A1" as
an example of a cell address and said to use a "helper cell" in the comment.
I also said it had to be linked to whatever sheet the OP wanted the end
result. And "Text Box 1" is the name Excel gave to that Text Box, not one I
gave it, so that is what I used to identify it in the code. I agree about
leaving spaces out of names, AndDoItMyself on the names I create.

If this is your offering, and it will do the job the OP wants, then please
post directions as to how it can be implemented. I have other applications
where it could be used as well.

It'd be a lot less trouble just to set the Linked Cell property of the
text box to the address of the cell that should contain its text.

Thanks,
Vaya con Dios,
Chuck, CABGx3

CLR wrote...

This code will get the text from a Text Box to a helper cell on the same
sheet.....then that cell can be linked to whatever sheet you want.....

Sub TextBoxToCell()
Range("a1").Value = [Text box 1].Text
End Sub ...

I am simply attempting to make a cell on the 2nd sheet of a workbook
automatically equal the value of a text box on the 1st sheet that is
used as an input page for the user to input data. None of the data is
used in any calculations. It is simply for header information and
descriptions.

It'd be a lot less trouble just to set the Linked Cell property of the
text box to the address of the cell that should contain its text.

Also, the macro above would fail for many reasons. First, spaces are
NEVER allowed in the names of controls. You may believe they add
'readability', but in this case that questionable belief is trumped by
implying functionality that just doesn't exist.

Next, OLE controls are specific to worksheets, so the only way the Text
property above works is when the containing the text box is the active
worksheet when the macro runs, in which case it'd put the .Text into
the *same* worksheet's A1 cell, so not much use when .Text should be
put into a cell in a different worksheet.

Finally, this may be due to an Excel version difference, but Excel 10
(XP) requires [TextBox1].Object.Text rather than [TextBox1].Text.

 

[Harlan Grove]

Maybe so Harlan, your language is only a light-year or so beyond mine, so I
don't understnad everything you say.........all's I know is that the code I
posted works in my Excel97 exactly as I stated in my post. I used "A1" as
an example of a cell address and said to use a "helper cell" in the
comment.
I also said it had to be linked to whatever sheet the OP wanted the end
result. And "Text Box 1" is the name Excel gave to that Text Box, not one
I
gave it, so that is what I used to identify it in the code. I agree about
leaving spaces out of names, AndDoItMyself on the names I create.

OK, then it doesn't work in Excel 10/XP. The OP didn't mention his/her Excel
version, so if s/he's also using Excel 8/97, then no problem. Important to
specify that your macros would have only been tested in a fairly old Excel
version, and you should remember that there are more differences between VBA
in Excel 97 and all subsequent versions than there are in worksheet
formulas.

If this is your offering, and it will do the job the OP wants, then please
post directions as to how it can be implemented. I have other applications
where it could be used as well.

In design mode, right click on the text box, select Properties from the
pop-up dialog, in the LinkedCell property enter the address of the cell in
which the text box's text should appear.

 

[CLR]

Hi Harlan......

In design mode, right click on the text box, select Properties from the
pop-up dialog, in the LinkedCell property enter the address of the cell in
which the text box's text should appear.

Hmmmm......thanks for coming back with the explanation Harlan, but that must
be another Excel 10/XP thing, as it don't seem to work in 97 or 2k.....I
don't have the "LinkedCell property" option in either version. But no
matter.....the OP now has my code to use if he has 97 or 2k and your
"LinkedCell property" to use in case he has XP.......hopefully one of those
will cover him.

Vaya con Dios,
Chuck, CABGx3