Populate userform with values from different worksheet

[Mikeymike_W]

Hi All,

I have a userform with several textboxes which I would like to populate using the values on another workbook.

The workbook I want to pull the values from is located here:

C:\Users\MWa\Desktop\VBA Test" & txtCDNumber.Value & ".xlsm"

I would like the above file to be opened in the background and closed once the values are extracted.

How would I reference the cell values in this workbook to populate the userforms different fields?

Many thanks for any help you can provide.

Mike

 

[Roderick_E]

Once you have the workbook open...

dim wbkname as string
wbkname = [COLOR=#333333]C:\Users\MWa\Desktop\VBA Test" & txtCDNumber.Value & ".xlsm"[/COLOR]
Set wbk = workbooks(wbkname)
'then to refer to specific sheet and cells....
userform1.textbox1 = wbk.sheets("whatever the sheet name is in quotes").range("A2")

 

[Mikeymike_W]

Hi Roderick,

Thanks for your help.

I am getting a "subscript out of range" error at

Set wbk = Workbooks(wbkname)

I've checked and double checked but the file name is correct. There is more than one sheet in the file I am pulling the data from, would I need to reference the particular sheet? If so would you be able to direct me?

Many thanks again,

Mike

 

[Mikeymike_W]

HI,

Managed to get it working, I used the code below

Dim wbkExternal                 As Workbook
Dim strExternalWBPathName       As String

strExternalWBPathName = "C:\Users\MWa\Desktop\VBA Test\" & txtCDNumber.Value & ".xlsm"
Set wbkExternal = Workbooks.Open(strExternalWBPathName)

Many thanks for your help,

Mike