Follow up question for Tom Ogilvy - re: linking a text box to an external workbook

[JENNA]

Thanks for your reply to my posting regarding, linking a text box to
an external workbook. You informed me that the destination file would
need to be opened first.

However - I still have the problem regarding linking the text box
within a userform to the external opened file.

I can easily link the text box to a cell in the same workbook as the
userform by using the following statement

Range("A5").Value = txtMyTextBox.Value

although I wish to link the input of this text box to a cell in a
worksheet of the external Excel workbook.

So whatever is typed into this text box gets dynamically entered into
the external file/sheet/cell.

Do you know how I can achieve this?

Thanks again for your help Tom.

 

[Tom Ogilvy]

Your example doesn't do that unless you put it in the change event. To
mimic your example

Workbooks("Book2.xls").Worksheets("Sheet9").Range("A5").Value =
txtMyTextBox.Value

if you want to do it in userform_initialize

Private Sub Userform_Initialize()
txtMyTextBox.ControlSource = _
Workbooks("Book2.xls").Worksheets("Sheet9"). _
Range("A5").Address(0,0,xlA1,True)
End sub

Or you can hand enter the reference in the controlsource property.
'[Book2.xls]Sheet9'!A5

 

[JENNA]

Thanks Tom - greatly appreciated.

Regards Jenna