Trying to use ComboBox to use supplier name and populate form in other sheet-CodePudding

on one sheet I have a list of suppliers and their details, I have a userfrom containing a combobox that automatically populates from the list of suppliers. In the columns next to the suppliers, I have details with address, phone number etc. What I am attempting to do is after the user makes the selection, I would like the code to take the details in the adjacent columns and fill in the form. I have tried using the lookup function however I am constantly being given an error stating that the object could not be found. Below is what I have so far


Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("RFQ Information")

'Take supplier name from combobox
'Copy row data in supplier sheet and paste (transposed) into form

    Dim xRg As Range
    Set xRg = Worksheets("Suppliers").Range("A2:H15")
    Set Cells(53, 1) = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)
    
    Unload Me


End Sub

Private Sub UserForm_Initialize()
    Dim SupplierName As Range
    Dim SupSheet As Worksheet
    Dim tbl As ListObject
    Dim SupArray As Variant
    Dim SupString As String
    
    Set SupSheet = Sheets("Suppliers")
    Set tbl = SupSheet.ListObjects("Table1")
    Set SupplierName = tbl.ListColumns(1).DataBodyRange
    
    
    
    SupArray = SupplierName.Value
    ComboBox1.List = SupArray
    UserForm1.Show
    
    MsgBox ("done")
End Sub

CodePudding user response：

I would recommend using the ComboBox Change event instead of a button, since you want the info on list selection. You can also take advantage of the ComboBox.ListIndex property to get the selected item's location in the list, and then use that to get adjacent values from your data table. Here's a quick example of how to do so:

Private Sub ComboBox1_Change()
    
    Dim wb As Workbook:     Set wb = ThisWorkbook
    Dim wsSup As Worksheet: Set wsSup = wb.Worksheets("Suppliers")
    Dim rData As Range:     Set rData = wsSup.ListObjects("Table1").DataBodyRange
    Dim i As Long:          i = Me.ComboBox1.ListIndex   1
    If i = 0 Then Exit Sub 'Nothing selected
    
    'Second number is the column
    '  Column 1 is the Supplier
    '  Column 2 is the next column (phone maybe?)
    '  Column 3 is the column after that (address maybe?)
    MsgBox rData.Cells(i, 2) & Chr(10) & _
           rData.Cells(i, 3)
    
    'Load the values you want into the necessary form controls
    
End Sub