Thread: Combobox to get data from another workbook

Could some one give me the code to Populate Userform Combobox from a defined list in another workbook.

When opening the Userform does it need to create code open the workbook

Thanks

Blane 245

Thanks for that it works great, what would would i need to do add to the code so that when I select suename from the combobox it also inserts the First Name in the text box next to it


This is the code I deleted from the combobox code before i entered your 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

This what your code now looks like the with the correct worbook and shet name

Option Explicit

Const cListFile As String = "Defined Name Lists.xls"    ' the file containing the combobox data
Const cListSheet As String = "EmployeeDetails"    ' the worksheet containing the list
Const cListColumn As String = "B"    ' the column containin gthe list data
Const cHasHeader As Boolean = False    ' does the list have a header in row 1


Private Sub UserForm_Initialize()

    ' open the workbook containing the data to load in combox1 read only
    ' and hide it
    Dim theWB As Workbook
    Set theWB = Workbooks.Open(Filename:=cListFile, _
                               ReadOnly:=True)
    theWB.Windows(1).Visible = False

    ' address the combox list
    Dim theSheet As Worksheet
    Set theSheet = theWB.Worksheets(cListSheet)

    ' loop through all of the list loading the values into the combobox
    Dim rw As Range
    For Each rw In theSheet.Range(cListColumn & ":" & cListColumn).Rows

        ' skip row 1 if there is a header
        If (rw.Row = 1 And cHasHeader) Then

            ' stop on the first blank cell
        ElseIf (rw.Cells(1, 1).Value = "") Then
            Exit For
        Else

            ' move the data from the list workbook to the combox
            cboSurname.AddItem rw.Cells(1, 1).Value
        End If
    Next rw

    ' close the workbook and release storage
    theWB.Close SaveChanges:=False
    Set theWB = Nothing
    Set theSheet = Nothing

End Sub

Blane 245

Is there anyway to stop people entering a new name directly into the combobox as I have a separate userform for this

Blane 245

The first name is contained in colomn A in the worksheet called EmployeeDetails in the "Defined Names List.xls" file.

I would prefer to have the first name in a seperate text box

Blane 245

I inserted the following code but get a Run-time error 1004
Method Open of Object Workbook failed

Option Explicit

Const cListFile As String = "Defined Name Lists.xls"    ' the file containing the combobox data
Const cListSheet As String = "EmployeeDetails"    ' the worksheet containing the list
Const cListColumn As String = "B"    ' the column containing the surname
Const cNameColumn As String = "A"    ' the column containing the first name
Const cRateColumn As String = "C"    ' the column containing the rate date
Const cHasHeader As Boolean = False    ' does the list have a header in row 1

Dim Names() As String ' array to hold the first names
Dim Rates() As Single ' array to hold the hourly rates


Private Sub cboSurname_Click()

    ' get the index of the item selected in th combobox
    Dim theIndex As Long
    theIndex = Me.cboSurname.ListIndex + 1
    
    ' update the frist name and hourly rate text boxes
    Me.txtFirstName.Text = Names(theIndex)
    Me.txtHourlyRate.Value = Rates(theIndex)
    
'    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

Private Sub UserForm_Initialize()

    ' open the workbook containing the data to load in combox1 read only
    ' and hide it
    Dim theWB As Workbook
    Set theWB = Workbooks.Open(Filename:=cListFile, _
                               ReadOnly:=True)
    theWB.Windows(1).Visible = False

    ' address the combox list
    Dim theSheet As Worksheet
    Set theSheet = theWB.Worksheets(cListSheet)

    ' array size counter
    Dim nList As Long
    nList = 0
    
    ' loop through all of the list loading the values into the combobox
    Dim rw As Range
    For Each rw In theSheet.Rows

        ' skip row 1 if there is a header
        If (rw.Row = 1 And cHasHeader) Then

            ' stop on the first blank cell
        ElseIf (rw.Cells(1, 1).Value = "") Then
            Exit For
        Else

            ' move the data from the list workbook to the combox
            cboSurname.AddItem rw.Cells(1, cListColumn).Value
            
            ' retain the first name and hourly rate
            nList = nList + 1
            ReDim Preserve Names(1 To nList)
            ReDim Preserve Rates(1 To nList)
            Names(nList) = rw.Cells(1, cNameColumn).Value
            Rates(nList) = rw.Cells(1, cRateColumn).Value
        End If
    Next rw

    ' close the workbook and release storage
    theWB.Close SaveChanges:=False
    Set theWB = Nothing
    Set theSheet = Nothing

End Sub