Two Combobox Change Events

NutCase

Board Regular
Joined
Dec 10, 2004
Messages
60
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:

Code:
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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...
 
Upvote 0
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;

Code:
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
 
Upvote 0
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!!
 
Upvote 0
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;

Code:
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() )
 
Upvote 0
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!!! :)
 
Upvote 0
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.
 
Upvote 0
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;

Code:
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;

Code:
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
 
Upvote 0
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!?!?!
 
Upvote 0

Forum statistics

Threads
1,218,835
Messages
6,144,747
Members
450,567
Latest member
Mplz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top