Dim AData, BData, wsSource As Worksheet, ComboBoxData
Private Sub UserForm_Initialize()
Dim S As String, I As Long, SS As String
Set wsSource = ThisWorkbook.Worksheets("SHIP")
AData = wsSource.Range("E3:F" & wsSource.Cells(Rows.Count, 5).End(xlUp).Row).Value
AData = Application.Index(AData, Evaluate("ROW(1:" & UBound(AData, 1) & ")"), [{1,2}])
BData = wsSource.Range("H3:I" & wsSource.Cells(Rows.Count, 8).End(xlUp).Row).Value
BData = Application.Index(BData, Evaluate("ROW(1:" & UBound(BData, 1) & ")"), [{1,2}])
For I = 1 To UBound(AData)
If InStr(S & ",", "," & AData(I, 1) & ",") = 0 Then S = S & "," & AData(I, 1)
Next I
For I = 1 To UBound(BData)
If InStr(SS & ",", "," & BData(I, 1) & ",") = 0 Then SS = SS & "," & BData(I, 1)
Next I
For I = 1 To 5
Controls("ComboBox" & I).Clear
Next I
With Controls("ComboBox1")
.List = Split(Mid(S, 2), ",")
.ListIndex = -1
End With
ComboBox4.List = wsSource.Range("K3:K" & wsSource.Cells(Rows.Count, 11).End(xlUp).Row).Value
ComboBox5.List = wsSource.Range("L3:L" & wsSource.Cells(Rows.Count, 12).End(xlUp).Row).Value
End Sub
Private Sub ComboBox1_Change()
Dim n As Long
For n = 2 To 3
Controls("ComboBox" & n).Clear
Next n
For n = 4 To 5
Controls("ComboBox" & n).Value = ""
Next n
Call HandleComboBox(1)
Call HandleComboBox(2)
End Sub
Private Sub HandleComboBox(I As Long)
If Controls("ComboBox" & 1).ListIndex > -1 Then
Dim S As String, II As Long, M As Long
ComboBoxData = IIf(I = 1, AData, BData)
For M = 1 To UBound(ComboBoxData)
For II = 1 To 1
If ComboBoxData(M, II) <> Controls("ComboBox" & II).Value Then Exit For
Next II
If II = 1 + 1 And InStr(S & ",", "," & ComboBoxData(M, II) & ",") = 0 Then S = S & "," & ComboBoxData(M, II)
Next M
Controls("ComboBox" & I + 1).List = Split(Mid(S, 2), ",")
End If
End Sub