Add items in dependent comboboxes

[Aretradeser]

In an Excel sheet I have data sorted in titled columns.
In column "B", COUNTRY ORIGIN, there are many countries.
In column "C", ORIGIN CITY, many cities, but always belonging to the country in the same row of column "B".
In column "F", DESTINATION COUNTRY, there are many countries.
In column "G", DESTINATION CITY, there are many cities that also belong to the country that is in the same row of column "F".
In a Userform that I use for data entry, there are ComboBox1 (CountryOrigin) and ComboBox2 (CityOrigin), ComboBox3 (CountryDestination) and ComboBox4 (CityDestination).
What I need is a macro code that does the following:
1.- That when loading the ComboBox1 it adds all the countries of the column "B", without repeating countries; that is to say, unique values.
2.- That the ComboBox2 adds only the cities of the column "C", without repeating; but belonging to the country that we have selected in the ComboBox1.
3.- That when the ComboBox3 is loaded, it adds all the countries in column "F, without repeating countries; that is to say, unique values.
4.- That the ComboBox4 adds only the cities in column "G", without repeating cities; but belonging to the country that we have selected in the ComboBox3.
Thank you very much

 

[Akuini]

@Aretradeser
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[Aretradeser]

@Aretradeser
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

I EDIT THE INITIAL POST
In an Excel sheet, I have the data sorted in columns with headings.
In column "B", COUNTRY OF ORIGIN, there are records from several countries.
In column "C", CITY OF ORIGIN, there are records with several cities; but they are always cities of the country that is registered in the same row of column "B".
In column "G", COUNTRY OF DESTINATION, there are records for several countries.
In column "H", CITY OF DESTINATION, there are records with several cities; but they are always cities of the country that is registered in the same row of column "G".
In a Userform that I use for data entry, I use ComboBox1 to load the Source Countries, ComboBox2 to load the Source Cities, ComboBox4 to load the Destination Countries and ComboBox5 to load the Destination Cities.
What I need is a macro code that does the following:
1.- That when loading the ComboBox1 it adds all the countries of the column "B", without repeating countries; that is to say, unique values.
2.- That the ComboBox2 adds only the cities of column "C", without repeating; but with the condition that they are cities belonging to the country that we have previously selected in ComboBox1.
3.- That when the ComboBox3 is loaded, it adds all the countries in column "F", without repeating countries; that is to say, unique values.
4.- That the ComboBox4 adds only the cities of the column "H", without repeating; but with the condition that they are cities belonging to the country that we have previously selected in ComboBox3.
I attach a link to this with an example Excel file.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you very much

 

[Akuini]

Your description doesn’t match your data in sheet BDATOS, for example :

In column "B", COUNTRY OF ORIGIN, there are records from several countries.

but col B is PAIS DE DESTINO.
Other columns also don't match.

 

[Aretradeser]

Your description doesn’t match your data in sheet BDATOS, for example :

but col B is PAIS DE DESTINO.
Other columns also don't match.

Sorry for the mistake. These are the columns:
Col “B”, PAIS DE DESTINO
Col “C”, CIUDAD DESTINO
Col “G”, PAIS DE ORIGEN
Col “H”, CIUDAD ORIGEN

In an Excel sheet, I have the data arranged in columns with headings. In column "B", PAIS DE DESTINO, there are records for several countries. In column "C", CIUDAD DESTINO, there are records with several cities; but they are always cities of the country that is recorded in the same row of column "B". In column "G", PAIS DE ORIGEN, there are records for several countries. In column "H", CIUDAD ORIGEN, there are records with several cities; but they are always cities of the country that is registered in the same row of column "G". In a user form, which I use for data entry, I use 4 ComboBoxes to load the countries of origin, the cities of origin, the countries of destination and the cities of destination. What I need is a macro code that does the following: 1.- That in a ComboBox all the countries of the column "B" are loaded, without repeating countries; that is to say, unique values. 2.- That in another ComboBox it adds only the cities of column "C", without repeating; but with the condition that they are cities belonging to the country that we have previously selected in the ComboBox where we have loaded the destination countries. 3.- That in the third ComboBox, all the countries of column "F" are loaded, without repeating countries; that is to say, unique values. 4.- That the fourth ComboBox adds only the cities of column "H", without repeating; but with the condition that they are cities belonging to the country that we have previously selected in the third ComboBox, where we have loaded the countries of origin. I attach a link to this with an example Excel file.

 

[Akuini]

Still confusing, but I populated:
combobox1 with unique values from col B (PAIS DE DESTINO), even though the label on the left says PAÍS DE ORIGEN
combobox2 with values from col C, filtered by combobox1
combobox4 with unique values from col G
combobox5 with values from col H, filtered by combobox4

Here's the code:

Dim dar As Object
Dim va, vb, vc, vd
Dim n As Integer

Private Sub ComboBox1_Change()
'VisibilidadControles
ComboBox2.Value = ""
End Sub

Private Sub ComboBox4_Change()
ComboBox5.Value = ""
End Sub

Private Sub UserForm_Initialize()
    With Sheets("BDATOS")
        n = .Range("B" & Rows.Count).End(xlUp).Row
        va = .Range("B5:B" & n)
        vb = .Range("C5:C" & n)
        vc = .Range("G5:G" & n)
        vd = .Range("H5:H" & n)
    End With
    Set dar = CreateObject("System.Collections.Arraylist")

End Sub

Private Sub ComboBox1_Enter()
Dim x
    dar.Clear
    For Each x In va
        If Not dar.Contains(x) Then dar.Add CStr(x)
    Next
    dar.Sort
ComboBox1.List = dar.toArray()
End Sub

Private Sub ComboBox2_Enter()
    Dim i As Long, tx As String
    dar.Clear:    tx = UCase(ComboBox1)
    For i = LBound(va) To UBound(va)
        If UCase(va(i, 1)) = tx Then
           If Not dar.Contains(vb(i, 1)) Then dar.Add vb(i, 1)
        End If
    Next
    dar.Sort
ComboBox2.List = dar.toArray()
End Sub

Private Sub ComboBox4_Enter()
Dim x
    dar.Clear
    For Each x In vc
        If Not dar.Contains(x) Then dar.Add CStr(x)
    Next
    dar.Sort
ComboBox4.List = dar.toArray()
End Sub

Private Sub ComboBox5_Enter()
    Dim i As Long, tx As String
    dar.Clear:    tx = UCase(ComboBox4)
    For i = LBound(vc) To UBound(vc)
        If UCase(vc(i, 1)) = tx Then
           If Not dar.Contains(vd(i, 1)) Then dar.Add vd(i, 1)
        End If
    Next
    dar.Sort
ComboBox5.List = dar.toArray()
End Sub

Note:
- I removed your existing code because I only want to show you the code to meet your requirements. If it works & you want to use it then of course you need to combine it with your existing code.
- You don't need data in sheet APOYO.

Example:

Aretradeser 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[Aretradeser]

Thank you, Akuini, for your brilliant solution.
I need to solve two issues:
1. When in the ComboBox1 select a country other than ESPAÑA or PORTUGAL, that the TextBox3 becomes inactive.
2. When registering the data, I need to fill in the following ones: ComboBox1, 2, 3, 4 and 5; and the TextBox1, 2, 3, 4 and 5, but taking into account, that the TextBox3 becomes inactive, when in the ComoboBox1, a country other than ESPAÑA or PORTUGAL has been selected.

'
Private Sub cmdbRegistrar_Click()
    Dim Salir As Boolean
    Dim fe1 As Date
    Dim fe2 As Date
    For n = 1 To 5: If Me.Controls("TextBox" & n) = "" Then Salir = True: GoTo Verifica
    Next
    For n = 1 To 5: If Me.Controls("ComboBox" & n) = "" Then Salir = True: GoTo Verifica
    Next
Verifica:
    If Salir Then MsgBox "FALTAN DATOS POR CUMPLIMENTAR !!!", vbExclamation: Exit Sub
    fe1 = CDate(TextBox4)
    TextBox4 = Format(fe1, "mm/dd/yyyy")
    If Not IsDate(TextBox5) Then
        MsgBox "INGRESE UNA FECHA VÁLIDA", vbExclamation
        TextBox5.SetFocus
        Exit Sub
    End If
    fe2 = CDate(TextBox5)
    TextBox5 = Format(fe2, "mm/dd/yyyy")
    Sheets("BDATOS").Select
    Sheets("BDATOS").Unprotect ("123")
    With Worksheets("BDATOS")
        t = Cells(Rows.Count, 2).End(xlUp).Row
        Cells(t + 1, 2) = ComboBox1
        Cells(t + 1, 3) = ComboBox2
        Cells(t + 1, 4) = TextBox4.Value
        Cells(t + 1, 5) = TextBox5.Value
        Cells(t + 1, 6) = TextBox2 & " " & TextBox3 & ", " & TextBox1 
        Cells(t + 1, 7) = ComboBox4
        Cells(t + 1, 8) = ComboBox5
        Cells(t + 1, 9) = ComboBox3
    End With
    Cells(t, 9).Select
    Selection.AutoFill Destination:=Range("I" & t & ":I" & t + 1), Type:=xlFillDefault
    Sheets("BDATOS").Protect ("123")
    For n = 1 To 9
        On Error Resume Next
        Controls("textbox" & n) = ""
        Controls("combobox" & n) = ""
    Next
    Sheets("DATOS").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

Aretradeser 2.xlsm

Shared with Dropbox

www.dropbox.com