Two Combobox Change Events

[NutCase]

I have a Userform on which there are two comboboxes and a label. I want the user to be able to select from either the job name (combobox1) or a job number (combobox2). When either combobox is selected, I need the relevant data displayed in both comboboxes and the label. I have the following code below:

Private Sub ComboBox1_Change()
Dim MyValue As String
Dim MyLookup As Double

    MyValue = ComboBox1.Value
    Label1 = Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:C65536"), 2, False)
    ComboBox2 = Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:c65536"), 3, False)
End Sub

Private Sub ComboBox2_Change()
Dim MyValue As String
Dim MyLookup As Double

    MyValue = ComboBox2.Value
    Label1 = Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:C65536"), 2, False)
    ComboBox1 = Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:c65536"), 1, False)
End Sub

This is giving me an error message:
Run-time error '1004'
Unable to get the VLookup property of the WorksheetFunction class

I'm guessing this may be to do with having two change events that relate to the same Comboboxes?

 

[Fat Cat]

Ok, no worries,

This code (below) is based on a UserForm with;
ComboBox1
ComboBox2
Label1
CommandButton named "ExitButton"

The data is on a sheet named "JobList" in columns A, B & C
Row 1 contains header labels; "Select Job Name", "Select Customer Name" & "Select Job Number"
Row 2 onwards contains the actual data

Private Sub ComboBox1_Change()
    ComboBox2.ListIndex = ComboBox1.ListIndex           'set both comboboxes to show the same row
    Label1.Caption = Cells(ComboBox1.ListIndex + 1, 2)  'copy customername to Label1
    
    'Note: the words "Select Job Name" is in ComboBox position 0
    'the actual data to select is in positions 1 onwards in the ComboBox
    'However, the data on the sheet starts at row 2 (1 is the headings)
    'therefore the correct Row to get data from is ListIndex + 1
End Sub

Private Sub ComboBox2_Change()
    ComboBox1.ListIndex = ComboBox2.ListIndex           'set both comboboxes to show the same row
    Label1.Caption = Cells(ComboBox2.ListIndex + 1, 2)  'copy customername to Label1
End Sub

Private Sub ExitButton_Click()
    End     'they want to leave so stop the macro
End Sub

Private Sub UserForm_Activate()
    Dim LastRow As Long         'variable to store the last row with data
    Dim FirstRow As Long        'variable to store the first row

    FirstRow = 1                'header row with "Select Job Name" etc
    LastRow = Sheets("JobList").Range("A65536").End(xlUp).Row   'find last used row

    For i = FirstRow To LastRow         'loop through the data adding it to the comboboxes
        ComboBox1.AddItem Cells(i, 1)   'note: data from the sheet goes to the same rows in the 2 comboboxes
        ComboBox2.AddItem Cells(i, 3)   'note: Cells( row number , col number )
    Next i
        ComboBox1.ListIndex = 0         'once filled set ListIndex to display position 0 (the headers)
        ComboBox2.ListIndex = ComboBox1.ListIndex
End Sub

Because we loaded both ComboBoxes at the same time and sent the bits of data to the same row in each, we can simply use the selected row number in the combobox to let us know which row on the sheet to get the customer name from. No need for searching through the data. For example data from row 2 on the sheet went to position 1 in combobox1 and position 1 in combobox 2. Therefore if they select position 1 in either combobox, we know the data came from row 2 on the spreadsheet and that is the row to get the customer name from (ie: ComboBox1.ListIndex + 1 = 1 + 1 = 2 )