Get to use closed workbook dat in vba lookup

[TurboDieselOne]

Hi I have the following code

 Private Sub Userform_Activate() [code]
[code]On Error Resume Next [code]
[code]TextBox1.Value = ActiveCell [code]
[code]Search = Application.WorksheetFunction.VLookup(Left(RightTextBox1, 21), 6), Range("Notessearch"), 16, False) [code]
[code]TextBox1.Value = ActiveCell & Chr(10) & "----------" & Chr(10) & Search [code]
[code]On Error GoTo 0[code]
End Sub
 
 
how can i change this to open a workbook then lookup in the range "Notesearch" in this workbook apply the data to the :-
 
TextBox1.Value = ActiveCell & Chr(10) & "----------" & Chr(10) & [B]Search[/B]
 
then close the Workbook
 
ActiveCell is on this workbook but the data for [B]Search[/B] is on the closed workbook
 
 
Thanks

 

[Trevor G]

Try this out, behind your form on initialise or activate

Private Sub UserForm_Initialize()
Workbooks.Open "C:\Path to Workbook\name of workbook.xls"
Application.Goto Reference:="NoteSearch"
ActiveCell.Copy
ActiveWorkbook.Close
Me.TextBox1.Paste
End Sub