Another newbie question:

I can't figure out how to copy text from a particular cell on Sheet1
and paste it into my text box, everything I've tried hasn’t worked. The
idea is so the user can edit the text without having to retype it.

I can copy text from the text box but cannot retrieve it, here's what I
have so far.


Private Sub cmdSave_Click()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Names Birth Dates")
Set ws2 = Worksheets("BDS Under Construction")

'Retrieve text from the correct cell location.

'copy text back to the same cell location.
ws1.Cells((ws2.Cells(2, 87)) - 3, 3) = Me.BdayText.Value

End Sub


Any comments are greatly appreciated.

Matt


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=552797

Flintstone wrote:
Another newbie question:

I can't figure out how to copy text from a particular cell on Sheet1
and paste it into my text box, everything I've tried hasn't worked.

Hi Matt,

There's not enough info for me to be specific, however, if I had a Text
Box on Sheet2 (Excel has given it the name "Text Box 1") and I wanted
it to show the contents of Sheet1 A1 then I would use the following...

Sheet2.Shapes("Text Box 1").TextFrame.Characters.Text = Sheet1.Cells(1,
1).Value

Does this help?

Ken Johnson

Ken:

Saw your post, thought I'd give it a try (to better learn things...)
First of all I thought it would be (based on your narrative explanation:

Sheet1.Cells(1,1).Value =
Sheet2.Shapes("TextBox1).TextFrame.Characters.Text

Also assumed textbox was created using the Control Toolbox;

Anyway - it doesn't work - Here is my code:

Sub Doit()
Sheet1.Cells(1, 1).Value =
Sheet2.Shapes("TextBox1").TextFrame.Characters.Tex t
End Sub

Any thouhts?

TIA,

Jim



"Ken Johnson" wrote in message
oups.com:

Flintstone wrote:

Another newbie question:

I can't figure out how to copy text from a particular cell on Sheet1
and paste it into my text box, everything I've tried hasn't worked.


Hi Matt,

There's not enough info for me to be specific, however, if I had a Text
Box on Sheet2 (Excel has given it the name "Text Box 1") and I wanted
it to show the contents of Sheet1 A1 then I would use the following...

Sheet2.Shapes("Text Box 1").TextFrame.Characters.Text = Sheet1.Cells(1,
1).Value

Does this help?

Ken Johnson

JimMay wrote:
Ken:

Saw your post, thought I'd give it a try (to better learn things...)
First of all I thought it would be (based on your narrative explanation:

Sheet1.Cells(1,1).Value =
Sheet2.Shapes("TextBox1).TextFrame.Characters.Text

Also assumed textbox was created using the Control Toolbox;

Anyway - it doesn't work - Here is my code:

Sub Doit()
Sheet1.Cells(1, 1).Value =
Sheet2.Shapes("TextBox1").TextFrame.Characters.Tex t
End Sub

Any thouhts?

Hi Jim,

I just used a standard TextBox. I steer clear of the Control Toolbox, I
mostly want my stuff to work on PC and Mac, the school I teach at has
both on the network. I'm not sure though whether that has anything to
do with it.

Also, there are typos in
Sheet2.Shapes("TextBox1).TextFrame.Characters.Text

It should read...

Sheet2.Shapes("Text Box 1").TextFrame.Characters.Text

ie, 2 missing spaces and a "

Could it be that perhaps?

I changed my code to do what yours does, which is transfer data from
Text Box to Sheet1 A1, rather than the other way around and it worked
OK (after I included the two missing spaces).

Ken Johnson

Thanks guys, but I've find a different solution by using the Propertie
window, instead of code I typed this into the ControlSource of the tex
box.

=INDIRECT("'Names Birth Dates'!"&CH2&CI2-3)

CH2 is the column letter and CI2 is the row number; works like a charm
However, for the sake of learning I'll have to try it your way.

Again thanks.

Mat

--
Flintston
-----------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...fo&userid=1531
View this thread: http://www.excelforum.com/showthread.php?threadid=55279