Populate combobox based on criteria

[SimondsJM]

I have a combobox to populate but the population depends on the value of another combobox.

First I need it find the value of CBox1 in column B, then offset to column C and populate with any values going down til it hits a blank space


Item 1...|
........... | Item A
............| Item B
Item 2...|
............| Item C
(ignore periods they are there to indicate spaces)

So if I select Item 1 in CBox1 then CBox 2 should populate with (Item A, and Item B)

Any help would be greatly appreciated.

 

[Domenic]

Maybe something like this... If the ComboBoxes are used in a UserForm, place the following code in the UserfForm module...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Dim[/color] CloseForm [color=darkblue]As[/color] [color=darkblue]Boolean[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ComboBox1_Exit([color=darkblue]ByVal[/color] Cancel [color=darkblue]As[/color] MSForms.ReturnBoolean)
    [color=darkblue]Dim[/color] vData [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] rFoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] SearchTerm [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]If[/color] CloseForm = [color=darkblue]True[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] Me.ComboBox1.Value = "" [color=darkblue]Then[/color]
        MsgBox "Please enter or select a value for ComboBox1...", vbExclamation
        Cancel = [color=darkblue]True[/color]
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    SearchTerm = Me.ComboBox1.Value
    [color=darkblue]Set[/color] rFoundCell = Columns("B").Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    [color=darkblue]If[/color] rFoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MsgBox "Search term was not found...", vbExclamation
        [color=darkblue]With[/color] Me.ComboBox1
            .SelStart = 0
            .SelLength = Len(.Value)
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        Cancel = [color=darkblue]True[/color]
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]If[/color] rFoundCell.Offset(2, 1) <> "" [color=darkblue]Then[/color]
        vData = Range(rFoundCell.Offset(1, 1), rFoundCell.Offset(1, 1).End(xlDown))
        Me.ComboBox2.List = vData
    [color=darkblue]Else[/color]
        [color=darkblue]With[/color] Me.ComboBox2
            .Clear
            .AddItem rFoundCell.Offset(1, 1).Value
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_QueryClose(Cancel [color=darkblue]As[/color] [color=darkblue]Integer[/color], CloseMode [color=darkblue]As[/color] [color=darkblue]Integer[/color])
    If CloseMode = 0 [color=darkblue]Then[/color] CloseForm = [color=darkblue]True[/color]
End [color=darkblue]Sub[/color]
[/font]

Change the control names (ComboBox1 and ComboBox2), accordingly.

 

[SimondsJM]

The code works very well except one issue.

The form is called from one sheet but looks at data in another. when I open the form i can select the combobox1 just fine cause that code point to the correct sheet, i just don't know how to add that in to this code.

 

[Domenic]

Unclear... Can you please clarify?

 

[SimondsJM]

Sorry for the confusion, but i believe i have found what i was looking for.

Private Sub CBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim vData As Variant
    Dim rFoundCell As Range
    Dim SearchTerm As String
    If CloseForm = True Then Exit Sub
    If Me.CBox1.Value = "" Then
        MsgBox "Please enter or select a value for ComboBox1...", vbExclamation
        Cancel = True
        Exit Sub
    End If
    SearchTerm = Me.CBox1.Value
    Set rFoundCell = Sheets("Vendor List").Columns("B").Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If rFoundCell Is Nothing Then
        MsgBox "Search term was not found...", vbExclamation
        With Me.CBox1
            .SelStart = 0
            .SelLength = Len(.Value)
        End With
        Cancel = True
        Exit Sub
    End If
    If rFoundCell.Offset(2, 1) <> "" Then
        vData = Range(rFoundCell.Offset(1, 1), rFoundCell.Offset(1, 1).End(xlDown))
        Me.CBox2.List = vData
    Else
        With Me.CBox2
            .Clear
            .AddItem rFoundCell.Offset(1, 1).Value
        End With
    End If
   End Sub

Slight modification.

Issue was that the form launches from Sheet1 "Master List", but the combobox "CBox2" is pulling data from a different sheet "Vendor List"

So i had to add in for the code to look at the data on the "Vendor List" Sheet.

 

[Domenic]

Glad you've sorted it out. Thanks for the feedback.