excelvbacomboboxmatchuserform

Excel VBA Userform Combobox


I am trying to populate a combobox with a list of letters but only those that do not appear in another range

The full list is held in a range named 'prbCodes' and I populate the combobox using this

For Each cClass In Range("prbCodes")
With Me.ddSelect
    .AddItem Format(cClass.Value, "")
  End With
Next cClass

The probes id letters in use are held in a named range 'prbList', how to I compare/match the two ranges so that only the letters that do not appear in the 'prbList' are shown in ddSelect

Thanks

EDIT: The combobox is part of a userform, and the data is held on a sheet called 'Data'.

The two named column ranges, "prbList" and "prbCodes" are part of the data sheet. prbCodes contains the letters A-Z and numbers 0-9. prbList is a column in a table, 'tbl_probes' and contains the letters that have been used.


Solution

  • Populate Combobox Conditionally

    Private Sub UserForm_Initialize()
        
        ThisWorkbook.Activate
        
        Dim rgCodes As Range: Set rgCodes = Range("prbCodes")
        Dim rgList As Range: Set rgList = Range("prbList")
        
        Dim cell As Range, Code As String
        
        With Me.ddSelect
            .Clear
            .ColumnWidths = "50;50"
            .ColumnCount = 2
            For Each cell In rgCodes.Cells
                Code = CStr(cell.Value)
                If IsError(Application.Match(Code, rgList, 0)) Then
                    .AddItem Code
                    .List(.ListCount - 1, 1) = CStr(cell.Offset(0, 1).Value)
                End If
            Next cell
        End With
        
    End Sub
    

    enter image description here enter image description here