Select a value in a combobox, remove same value in another combobox

[ShieBoon]

HI ALL,

Let me explain the challenge i'm facing now.
I have 4 comboboxes on a userform. Each has the same set of values assigned to it when the form starts up.
Now...
Let's say the list of values are
1
2
3
4
5

When i select 1 in combobox 1, this should happen
list in combobox 1 - 1,2,3,4,5
list in combobox 2,3 and 4 - 2,3,4,5

Following from that, let's say i select 2 in combobox 2, this should happen
list in combobox 1 - 1,3,4,5
list in combobox 2 - 2,3,4,5
list in combobox 3 and 4 - 3,4,5

And then selecting 3 in combobox 3,
list in combobox 1 - 1,4,5
list in combobox 2 - 2,4,5
list in combobox 3 - 3,4,5
list in combobox 4 - 4,5

and so on.

Right now. I'm racking my brain over this and i can't seem to get the right solution. Feed me with ideas please!!

using xl2003

Thanks,
Shie Boon

 

[AlphaFrog]

Here's one way it could be done...

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 1 To 4
        Me.Controls("ComboBox" & i).List = Array("Apple", "Orange", "Pear", "Grape", "Strawberry")
    Next i
End Sub

Private Sub ComboBox1_Change()
    Updater ComboBox1
End Sub

Private Sub ComboBox2_Change()
    Updater ComboBox2
End Sub

Private Sub ComboBox3_Change()
    Updater ComboBox3
End Sub

Private Sub ComboBox4_Change()
    Updater ComboBox4
End Sub

Private Sub Updater(cb As ComboBox)

    Dim i As Integer, ii As Integer, cbox As ComboBox
    
    For i = 1 To 4
        If cb.Name <> "ComboBox" & i Then
            Set cbox = Me.Controls("ComboBox" & i)
            For ii = 0 To cbox.ListCount - 1
                If cbox.List(ii) = cb.Value Then
                    cbox.RemoveItem ii
                    Exit For
                End If
            Next ii
        End If
    Next i

End Sub

 

[noobie169]

Here's one way it could be done...

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 1 To 4
        Me.Controls("ComboBox" & i).List = Array("Apple", "Orange", "Pear", "Grape", "Strawberry")
    Next i
End Sub
 
Private Sub ComboBox1_Change()
    Updater ComboBox1
End Sub
 
Private Sub ComboBox2_Change()
    Updater ComboBox2
End Sub
 
Private Sub ComboBox3_Change()
    Updater ComboBox3
End Sub
 
Private Sub ComboBox4_Change()
    Updater ComboBox4
End Sub
 
Private Sub Updater(cb As ComboBox)
 
    Dim i As Integer, ii As Integer, cbox As ComboBox
 
    For i = 1 To 4
        If cb.Name <> "ComboBox" & i Then
            Set cbox = Me.Controls("ComboBox" & i)
            For ii = 0 To cbox.ListCount - 1
                If cbox.List(ii) = cb.Value Then
                    cbox.RemoveItem ii
                    Exit For
                End If
            Next ii
        End If
    Next i
 
End Sub

Hi, is it possible for you to create a sample excel file using your code and share the link here? Will be very helpful as I encountered a similar problem but I have difficulty solving the problem with just the codes.

Thanks in advance.

 

[AlphaFrog]

Create a userform with four comboboxes named ComboBox1, ComboBox2, ComboBox3, and ComboBox4.

Paste the code in the userform's module. That's it.

 

[noobie169]

Will try. Thanks for the hint

 

[noobie169]

It works for userform. However, I will need this to work on a worksheet and that is where I am encountering problems. Any advice?