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?

 

[Norie]

The error is probably more likely to be with the WorksheetFunction.Vlookup, as the error message is indicating.

Are you sure the values you are looking for are being found?

 

[NutCase]

Yes they are being found, as when I delete the combobox2 change event code and then try selecting something from combobox1 on the userform, it runs perfectly. It only encounters an error when trying to run two change events...

 

[Fat Cat]

I think you are right with the problem being the 2 change events. When you select something in ComboBox1 you trigger a change event in ComboBox2 which in turn triggers another change event back in ComboBox1.

Since both ComboBoxes change the caption of Label1 as the first thing they do, can you shift the populating of the 2nd combobox to occur after a change event on Label1 rather than the first combobox ?
You would need to somehow pass the ID of the calling ComboBox so that it knows which ComboBox to repopulate.

Something along the lines of;

Private Sub ComboBox1_Click()

Label1.Caption = "A:" & Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:C65536"), 2, False)
End Sub

Private Sub ComboBox2_Click()

Label1.Caption = "B:" & Application.WorksheetFunction.VLookup(MyValue, Worksheets("JobList").Range("A2:C65536"), 2, False)
End Sub

Private Sub Label1_Change()

If Left(Label1.Caption, 2) = "A:" Then
    ComboBox2 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("JobList").Range("A2:c65536"), 3, False)
Else
    ComboBox1 = Application.WorksheetFunction.VLookup(ComboBox2.Value, Worksheets("JobList").Range("A2:c65536"), 1, False)
End If
End Sub

 

[NutCase]

Hi Fat Cat,
I think you are definately pointing me in the right direction...
I have tried your code (deleting both combobox1 & 2 change events) and am getting the following error message:

Run-time error '9':
Subscript out of range

I don't have all that much knowledge of VBA - everything I have learnt has been from this forum, so if it is something really obvious, I apologise in advance for my limited and lacking knowledge!!

 

[Fat Cat]

Which line of code is giving the subscript error ?

If you Run the UserForm and when it pops up the error message, click on the DEBUG option and it will highlight in yellow the offending line of code. If you then click you cursor on each variable name on that line it should pop a small box up with the value that is currently in the variable.

From a quick glance it will be the MyValue in the VLookup functions for the ComboBox1 and ComboBox2 click events. Change this to;

Label1.Caption = "A:" & Application.WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("JobList").Range("A2:C65536"), 2, False)

and ComboBox2.Value for the 2nd combobox

By the way; what would happen if you simply used your existing code and instead of change events just used click events ? You may find it would work OK after all (ie: Private Sub ComboBox1_Click() )

 

[NutCase]

Hi Fat Cat,

This has fixed the error message I just mentioned, however, by changing the "Change" event to "Click" events has the same issue.

I am once again getting the error message:
Run-time error '1004'
Unable to get the VLookup property of the WorksheetFunction class



I know that we are headed in the right direction... But by having two click events it appears to act the same way as having two change events. Please keep the input coming!!!

 

[Norie]

What does the error message say?

It say's there has been an error with the Vlookup.

The error probably is linked to the change events, as they change the values in the combobox.

What values are you trying to lookup when you get the error.

 

[Fat Cat]

Really not sure what data you want to appear in ComboBox2 once you select something in ComboBox1.

Do you just want ComboBox2 to show the corresponding data from the next column over or do you want to re-populate the ComboBox2 with new data.

Depending on how you fill the ComboBoxes in the first place, it may be much quicker to just set the ListIndex property of the ComboBoxes.

IE: if you populate both comboboxes from data that is on the same rows, but different columns the following might do;

Private Sub ComboBox1_Change()
ComboBox2.ListIndex = ComboBox1.ListIndex
Label1 = ComboBox1.Value

End Sub

Private Sub ComboBox2_Change()
ComboBox1.ListIndex = ComboBox2.ListIndex
Label1 = ComboBox1.Value

End Sub

Couldn't get VLookup to work, but a workaround might be to use the Find function coupled with an Offset eg;

Private Sub ComboBox1_Change()

With Worksheets("JobList").Range("A2:C65536")
    Set c = .Find(ComboBox1.Value, LookIn:=xlValues)
End With
Label1 = "A:" & c.Offset(0, 0).Value

ComboBox2 = c.Offset(0, 1)

End Sub

Private Sub ComboBox2_Change()
With Worksheets("JobList").Range("A2:C65536")
    Set c = .Find(ComboBox2.Value, LookIn:=xlValues)
End With
Label1 = "B:" & c.Offset(0, 0).Value

ComboBox1 = c.Offset(0, -1)
End Sub

 

[NutCase]

The last formula using the offset method is more along the lines of what I need it to do...

So, to clarify:

On the sheet called "JobList" I have the following data:
Column A = Job Name
Column B = Customer Name
Column C = Job #

This relates to the comboboxes and label like this:
Combobox1 = Job Name
Combobox2 = Job #
Label1 = Customer Name

So when the user selects from the drop down list of Combobox1 a Job Name, I need for combobox2 to display the Job # (Data in Column C) and label1 to display the Customer Name (Data in Column B). Likewise, should the user select a job number from combobox2, I need for combobox1 to display the Job Name (Data in Column A), and the label1 to display the Customer Name (Data in Column B).
Hopefully this makes more sense now!?!?!