Populate Userform Combobox From Separate Workbook

**Mooseman60** · 10-25-2010, 10:46 AM

Can somee one tell me how to populate a combobox from a Defined List named Surname on worksheet called EmployeeDetails on a worksheet in a separate workbook named Defined Names List.xls

I have tried to enter the following in the Userform Combobox Properties Row Source

['Defined Name List.xls]EmployeeDetails'!$b$2:$b$50 but it doesn't like it

This is the Combobox code

Private Sub cboSurname_Click()
Dim Rw As Long
With Worksheets("EmployeeDetails")
Rw = .Range("B2:B50").Find(Me.cboSurname.Value, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False).Row
Me.txtFirstName.Text = .Range("A" & Rw).Value
Me.txtHourlyRate.Text = Format(.Range("c" & Rw).Value, "$#,##0.00")
End With
End Sub

Can someone point me in the right direction

**Leith Ross** · 10-25-2010, 11:10 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Mooseman60** · 10-25-2010, 07:47 PM

Populate Userform Combobox From Separate Workbook

--------------------------------------------------------------------------------

Can somee one tell me how to populate a combobox from a Defined List named Surname on worksheet called EmployeeDetails on a worksheet in a separate workbook named Defined Names List.xls

I have tried to enter the following in the Userform Combobox Properties Row Source

['Defined Name List.xls]EmployeeDetails'!$b$2:$b$50 but it doesn't like it

This is the Combobox code

Please Login or Register  to view this content.

Can someone point me in the right

**Leith Ross** · 10-25-2010, 08:57 PM

Hello Mooseman60,

Thanks for adding the code tags. I am not sure I follow your logic with using Find. A UserForm ComboBox performs look-ahead matching as you type and if the list is linked to a Range then why do you need Find? You should post your workbook so we can follow what you want to do and offer you meaningful solutions instead of educated guesses.

**Mooseman60** · 10-26-2010, 06:03 AM

Leith

What someone has suggested is that i should have separate files 1 xla for the userform code 1 xls for the Defined names and individual .xls files for saved as job number. (Each job number is saved as an .xls file) I think what the idea is is that the userform is only a shell for looking up the data and saving it as a new workbook I have attached my workbooks

Mooseman60

**romperstomper** · 10-26-2010, 06:39 AM

I would keep the form and the defined names in the same workbook. You can't use a Rowsource that refers to another workbook as far as I know, but I wouldn't use it anyway - I would load the data into the combobox using the List property instead.