Prevent repeitition of options in form drop down box

[Pauljj]

I have this code which is attached to the form, to help me idendify what columns the user should sort by. One problem, if you select a header in 'cbosort1' ideally it shouldn't come up when you go to 'cbosort2' could someone please guide me ?

There are 6 cbosort option but to give you the idea it is as follows for the first 2

With cbosort1
.AddItem "Booking Number"
.AddItem "Client Name"
.AddItem "Nights"
.AddItem "Status"
.AddItem "Supplier Code"
.AddItem "Arrival Date"
.AddItem "Supplier Name"
.AddItem "City"
.AddItem "Source"
.AddItem "Pg 2 Bkg Date"
End With

With cbosort2
.AddItem "Booking Number"
.AddItem "Client Name"
.AddItem "Nights"
.AddItem "Status"
.AddItem "Supplier Code"
.AddItem "Arrival Date"
.AddItem "Supplier Name"
.AddItem "City"
.AddItem "Source"
.AddItem "Pg 2 Bkg Date"
End With

 

[Pauljj]

Any ideas please????

 

[Jerry Sullivan]

You could loop through the Controls in your Userform and add the list items to Controls that have the specified Type and Name.

Private Sub UserForm_Initialize()
    Dim cCont As Control
    For Each cCont In Me.Controls
        If TypeName(cCont) = "ComboBox" Then
            If Left(cCont.Name, 7) = "cbosort" Then
                 With cCont
                    .AddItem "Booking Number"
                    .AddItem "Client Name"
                    .AddItem "Nights"
                    '....
                    .AddItem "Pg 2 Bkg Date"
                End With
            End If
        End If
    Next cCont
End Sub

You might consider storing your list items in a named range in your workbook to make it easier to revise the list.

 

[Pauljj]

Thanks for your reply Jerry, I did try this but if I select booking number from the first drop down, it still appears in the list when I go to the second drop down ?

 

[Jerry Sullivan]

Thanks for your reply Jerry, I did try this but if I select booking number from the first drop down, it still appears in the list when I go to the second drop down ?

Thank you for clarifying - I misunderstood that part.

There might be a better way to do this, but this seems to work.

Make an _Enter event for each ComboBox to call the function shown below.

Private Sub cbosort1_Enter()
  Call Load_Filtered_List(Me.Controls("cbosort1"))
End Sub
Private Sub cbosort2_Enter()
  Call Load_Filtered_List(Me.Controls("cbosort2"))
End Sub

Here are two functions that should be copied into the same module.

Public Function Load_Filtered_List(ByRef cboX As ComboBox)
    Dim arrAll() As String, arrRmv() As String
    Dim cCont As Control
    Dim i As Long
    arrAll = Split( _
        "Booking Number;Client Name;Nights;Status;" & _
        "Supplier Code;Arrival Date;Supplier Name;City;" & _
        "Source;Pg 2 Bkg Date", ";")
    ReDim arrRmv(1)
[COLOR="Teal"]    '---Make array of items selected in other comboboxes[/COLOR]
    For Each cCont In Me.Controls
        If TypeName(cCont) = "ComboBox" Then
            If Left(cCont.Name, 7) = "cbosort" And _
                cCont.Name <> cboX.Name Then
                 With cCont
                    If .Value <> "" Then
                        arrRmv(UBound(arrRmv)) = .Value
                        ReDim Preserve arrRmv(UBound(arrRmv) + 1)
                    End If
                End With
            End If
        End If
    Next cCont
[COLOR="teal"]    '---Add items to this combobox if not in arrRmv[/COLOR]
    With cboX
        .Clear
        For i = LBound(arrAll) To UBound(arrAll)
            If Not isMember(arrAll(i), arrRmv) Then
                .AddItem arrAll(i)
            End If
        Next i
    End With
End Function

Public Function isMember(strItem As String, ByRef strArr() As String) As Boolean
    Dim i As Long
    On Error Resume Next
    If IsError(LBound(strArr)) Then
        isMember = False
        Exit Function
    End If
    For i = LBound(strArr) To UBound(strArr)
        If strArr(i) = strItem Then
            isMember = True
            Exit Function
        End If
    Next i
    isMember = False
End Function

 

[Pauljj]

Thank you Jerry, apologies, I didn't see your latest message. Appreciate you getting back to me, I will check this out

Thanks again