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.

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



"(E-Mail Removed)" wrote:

> 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 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
....
>"(E-Mail Removed)" wrote:
>>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.

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




"Harlan Grove" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> ...
> >"(E-Mail Removed)" wrote:
> >>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" <(E-Mail Removed)> wrote...
>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.
>>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.

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.

Hi Harlan......

"Harlan Grove" <(E-Mail Removed)> wrote in message
>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






news:(E-Mail Removed)...
> "CLR" <(E-Mail Removed)> wrote...
> >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.
> >>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.
>
> 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.
>
>