Populate a list of values into a combobox dependent on another combobox "listindex"

[Gemitec]

Hello

1. I am trying to achive to select from combobox1 an item
2. this selected item will then be searched on a sheet
3. where the numbers matches, all the rows (values will be populated into another combobox)

On the picture you see, if I would select in combobox1 Nr2 (a3) on the sheet - > this will fill up the combobox1 with B3
but I need the values what are selected on the picture, since in columne E you see Nr2 Into Combobox2

So since A3 from combobox1 selected, therefore give back all values that match A3 in Columne E and populate the values next to it into another combobox... this case F3:F10 would be in the second combobox

Well any help is welcome and thank you

 

[SQUIDD]

Hi

Assuming you are actually using data validataion rather than combobox?

B3 is where the data validation list will go, by searching column E for the value from A3 and return the list from column F.

Dave

Sub INSERT_DATA_VALIDATION()

MyList = ""

For A = 1 To Range("F" & Rows.Count).End(xlUp).Row
    If Range("E" & A) = Range("A3") Then MyList = MyList & "," & Range("F" & A)
Next A

With Range("B3").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=MyList
End With

End Sub

 

[SQUIDD]

forgot to mention, but you will need to trigger the code via a sheet change event from your 1st data validation list.

 

[SQUIDD]

put this into the sheet code, this looks for the range A3 to change(your 1st validation box) then triggers the code to add the list to your second validation box

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("A3"), Range(Target.Address)) Is Nothing Then INSERT_DATA_VALIDATION

End Sub

 

[SQUIDD]

better still just put this into the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A3"), Range(Target.Address)) Is Nothing Or _
   Not Application.Intersect(Range("E:E"), Range(Target.Address)) Is Nothing Or _
   Not Application.Intersect(Range("F:F"), Range(Target.Address)) Is Nothing Then
        MyList = ""
        For A = 1 To Range("F" & Rows.Count).End(xlUp).Row
            If Range("E" & A) = Range("A3") Then MyList = MyList & "," & Range("F" & A)
        Next A
        With Range("B3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=MyList
        End With
End If
End Sub

 

[Gemitec]

Hello and thank you very much, well they are actually comboboxes
I let you know if this works

 

[Gemitec]

Hello, so since I used Comboboxes, I tried to put some coding together, and it seems like it works as I want it to.
I am very sorry not to test your code, because I am a newbee, and have no idea yet of your solution, but from some point of view, I am slowly checking this kind of workaround:
(this is a mess, I know, but it works (I hope))

If you choose from combobox1 a row (listindex), then it will search according to that in a range and populate the values it finds into next coombobox on userform: combobox4.

this is for combobox1 the code:


Private Sub ComboBox1_Change()

Dim Zahlungsart As Range
Dim ZahlungsartErgebnis As Variant
Dim ZahlungsartErgebnisTemp As Variant
Dim y As Integer
Dim search As Integer
Dim rangeF As Range

Zahlungsinformationen.ComboBox4.Clear

If Zahlungsinformationen.ComboBox1.Value = "-- Bitte Wählen --" Then
'only for testing
Else
y = Zahlungsinformationen.ComboBox1.ListIndex
If y < 0 Then
y = 0
End If
search = y + 1
End If

Dim rowcount As Long
rowcount = Worksheets("Einstellungen_Zahlung").Cells(Rows.Count, "E").End(xlUp).Row

cnt = 1

With Worksheets("Einstellungen_Zahlung")

For Each Zahlungsart In .Range(.Range("e2"), .Range("e" & .Rows.Count).End(xlUp))

If Zahlungsart.Value <> vbNullString And search = Zahlungsart Then

Set rangeF = .Cells(cnt + 1, "f")

If rangeF.Offset(, -1).Value = search Then

ZahlungsartErgebnis = Worksheets("Einstellungen_Zahlung").Cells(cnt + 1, "f").Value
Zahlungsinformationen.ComboBox4.AddItem ZahlungsartErgebnis

Else: End If

End If

cnt = cnt + 1

Next Zahlungsart

End With

End sub

Thank you very much for your work and time trying to help me

Regards