Help required with dual combo lookup

[StaffsLebowski]

Can anybody help with what seems like a simple problem?

I have two columns:

1 - Course Title (Has a combo box that references the range shown below)

2 - Course Reference

When a User selects a value from the Course Title combo box, the Course Reference (using the VLookup shown below) is automatically populated.

=IF(ISBLANK(C2),"",IF(LEN(VLOOKUP(C2,CourseList!$A$1:$B$65535,2,FALSE))>0,VLOOKUP(C2,CourseList!$A$1:$B$65535,2,FALSE),""))

What I'm trying to achieve, is to have two combo boxes. This will allow a User to select either a Course Title or a Course Refence and automatically populate the other field. This seemed quite strange forward at first but I keep getting a circular reference problem that I don't know how to get round.

Any solutions greatly appreciated.

Many thanks,

Staffs Lebowski

COURSE RANGE
----------------------------
CourseTitle: CourseRef:
----------------------------
MyCourse01 1
MyCourse02 2
MyCourse03 3

 

[Andrew Poulsom]

Where are your comboboxes from? If they are from Data Validation you won't be able to use formulas - they will be overwritten when the user makes a selection.

 

[StaffsLebowski]

Hi Andrew,

Thank you for your quick response. I'm primarily an Access Developer and somewhat 'challenged' in the world of Excel challenges!

I've inherited this problem and trying to find a solution. You're right, the Course Title combo box is from Data Validation. I was hoping to add a Course Reference combo box from Data Validation so the User could select from either combo box.

If this method is not possible, can you suggest another avenue to explore?

Once again, many thanks,

Warren.

 

[Andrew Poulsom]

First of all name your 2 lists CourseTitles and CourseRefs using Insert|Name|Define and assign those 2 names to your Data Validation cells (using the Allow box). Then right click the tab of the sheet that contains your Data Vaildation and choose View Code. Paste this code into the window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WF As WorksheetFunction
    Dim CourseRefs As Range
    Dim CourseTitles As Range
    Application.EnableEvents = False
    Set WF = WorksheetFunction
    Set CourseRefs = ThisWorkbook.Names("CourseRefs").RefersToRange
    Set CourseTitles = ThisWorkbook.Names("CourseTitles").RefersToRange
    With Target
        If .Address = "$C$2" Then
            If .Value = "" Then
                Range("D2").Value = ""
            Else
                Range("D2").Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))
            End If
        ElseIf .Address = "$D$2" Then
            If .Value = "" Then
                Range("C2").Value = ""
            Else
                Range("C2").Value = WF.Index(CourseTitles, WF.Match(.Value, CourseRefs, False))
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

The code assumes that your Data Validation is in C2 and D2 - adjust to suit. Close the Visual Basic Editor and try it out by selecting from one of the dropdowns.

 

[StaffsLebowski]

Hi Andrew,

Thank you for the time you have put into this. I was expecting to be pointed in a direction, not provided with a solution so this was a pleasant and much appreciated suprise!

I've followed your instruction to the letter, however, I can't get anything to work. I put a breakpoint on the code to see what was happening but it appears as if the 'Worksheet_Change' event is not even firing.

Macro security is set to 'Low'. Any suggestions what I'm doing wrong?

Kind regards,

Warren.

 

[Andrew Poulsom]

You did put the code in the module for the worksheet (eg Sheet1) didn't you? It won't work in a general module like Module1. Also what version of Excel do you have? maybe try typing this in the Immediate window in the VBE:

Application.EnableEvents = True

and pressing Enter.

 

[StaffsLebowski]

Hi Andrew,

To answer your questions first:

"You did put the code in the module for the worksheet (eg Sheet1) didn't you?" - Yes.

"Also what version of Excel do you have?" - 2003, SP3

I have now received the following error:

Runtime Error 1004
Unable to get the Match property of the WorksheetFunction class

This error appearead on line:

Range("D2").Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))

Investigation revealed the function...

WF.Match(.Value, CourseTitles, False)

...is where it failed. NB: The .Value parameter was a valid CourseRef

Kind regards,

Warren

 

[Andrew Poulsom]

Investigation revealed the function...

WF.Match(.Value, CourseTitles, False)

...is where it failed. NB: The .Value parameter was a valid CourseRef

The .Value property should be a valid CourseTitle.

 

[StaffsLebowski]

Hi Andrew,

I have this working now, perfectly. Thank you!

Can you tell me, how would you implement it for multiple rows? So a User could enter another course on another row?

Regards,

Warren.

 

[Andrew Poulsom]

I have amended the code so that it works with data validation pairs in the range C2:D11:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WF As WorksheetFunction
    Dim CourseRefs As Range
    Dim CourseTitles As Range
    If Target.Count > 1 Then Exit Sub
    If Application.Intersect(Target, Range("C2:D11")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Set WF = WorksheetFunction
    Set CourseRefs = ThisWorkbook.Names("CourseRefs").RefersToRange
    Set CourseTitles = ThisWorkbook.Names("CourseTitles").RefersToRange
    With Target
        If .Column = 3 Then
            If .Value = "" Then
                .Offset(, 1).Value = ""
            Else
                .Offset(, 1).Value = WF.Index(CourseRefs, WF.Match(.Value, CourseTitles, False))
            End If
        ElseIf .Column = 4 Then
            If .Value = "" Then
                .Offset(, -1).Value = ""
            Else
                .Offset(, -1).Value = WF.Index(CourseTitles, WF.Match(.Value, CourseRefs, False))
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub