Remove Item from Multiple ComboBoxes if it has been selected in another

I have a UserForm which contains 5 ComboBoxes. These ComboBoxes allow the user to select the outcome of the patient (more than one outcome is possible hence 5 ComboBoxes).

The ComboBoxes are populated with items from a named range. If an item is selected in the first ComboBox then I want it to be removed in the other ComboBoxes to prevent duplication. However I don't know how to do this?


Any help would be hugely appreciated.

My items are listed in column A in a worksheet titled "Outcomes":
I have to use a named range and not a fixed range as the user has the option to add a custom outcome which would get added to the last empty row of column A.

Here is my code at UserForm_Initialize which populates the comboboxes:

Dim ws As Worksheet
Dim LastRow As Long
Dim aCell As Range

Set ws = Worksheets("Outcomes")

With ws
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Each aCell In .Range("A1:A" & LastRow)
If aCell.Value <> "" Then
Me.cbOutcome1.AddItem aCell.Value
Me.cbOutcome2.AddItem aCell.Value
Me.cbOutcome3.AddItem aCell.Value
Me.cbOutcome4.AddItem aCell.Value
Me.cbOutcome5.AddItem aCell.Value
End If
Next
End With

Actually I don't think I've reference a named range I have just listed all cells in a column that aren't empty (code below):

Please Login or Register  to view this content.