VBA comboboxes

[ana1991]

Hello everyone

I have a question on vba comboboxes.

I have two combo boxes fullfilled with the same list of names. When I pick a name on the list of Combo box 1, how can I automatically exclude the picked name from the list on combo box 2, keeping the remaining names?

I'm using the following code to populate combo boxes 1 and 2:

For i = 1 To WorksheetFunction.CountA(Columns("A:A"))
UserForm1.ComboBox1F.AddItem ActiveCell.Offset(i - 1, 0) & "-" & ActiveCell.Offset(i - 1, 1)
UserForm1.ComboBox2.AddItem ActiveCell.Offset(i - 1, 0) & "-" & ActiveCell.Offset(i - 1, 1)
Next i



Thanks in advance

 

[EXCEL MAX]

Hello Ana,
try something like this...

Option Explicit

Dim I As Integer, varNLoops As Integer

Private Sub UserForm_Initialize()

    Range("A1") = "Item1"
    Range("A2") = "Item2"
    Range("A1").Activate
    For I = 1 To WorksheetFunction.CountA(Columns("A:A"))
        ComboBox1.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
        ComboBox2.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
    Next I

End Sub

Private Sub ComboBox1_Click()
    
    For varNLoops = 0 To ComboBox2.ListCount - 1
        If ComboBox1.Value = ComboBox2.List(varNLoops) Then
            ComboBox2.RemoveItem varNLoops
            Exit Sub
        End If
    Next

End Sub

Private Sub ComboBox2_Click()
    
    For varNLoops = 0 To ComboBox1.ListCount - 1
        If ComboBox2.Value = ComboBox1.List(varNLoops) Then
            ComboBox1.RemoveItem varNLoops
            Exit Sub
        End If
    Next
    
End Sub

 

[ana1991]

Hello Excel Max

Thanks for your help.
The code you wrote seems to work partially. For example when I pick A from the combobox 1, A doesn't appear in combobox2, which makes sense.
If after picking A on combobox 1, I pick B on 1, it disappears from combobox 2, but A continues to not appear on 2. How can I solve this issue?

Thanks in advance

Ana

 

[EXCEL MAX]

Hello Ana again,
if you still not resolved this problem here is solution...

Option Explicit

Dim I, varNLoops As Integer
'create collection
Dim varCollection As New Collection

Private Sub UserForm_Initialize()

    Range("A1") = "Item1"
    Range("A2") = "Item2"
    Range("A3") = "Item3"
    Range("A1").Activate
    For I = 1 To WorksheetFunction.CountA(Columns("A:A"))
        ComboBox1.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
        ComboBox2.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
    Next I
'fill the collection
    For varNLoops = 0 To ComboBox2.ListCount - 1
        varCollection.Add ComboBox2.List(varNLoops)
    Next
    
End Sub


Private Sub ComboBox1_Click()
'clear combobox
    ComboBox2.Clear
'fill combobox from collection
    For varNLoops = 1 To varCollection.Count
        ComboBox2.AddItem varCollection.Item(varNLoops)
    Next
'remove item
    For varNLoops = 0 To varCollection.Count
        If ComboBox1.Value = ComboBox2.List(varNLoops) Then
            ComboBox2.RemoveItem varNLoops
            Exit Sub
        End If
    Next
    
 End Sub

 

[mikerickson]

You might use code like

Private Sub ComboBox1_Click()
    ComboBox2.List = ComboBox1.List
    ComboBox2.RemoveItem ComboBox1.ListIndex
End Sub

 

[EXCEL MAX]

I can beleve.
Great solution Mr Mikerickson.

 

[ana1991]

Hello Ana again,
if you still not resolved this problem here is solution...

Option Explicit

Dim I, varNLoops As Integer
'create collection
Dim varCollection As New Collection

Private Sub UserForm_Initialize()

    Range("A1") = "Item1"
    Range("A2") = "Item2"
    Range("A3") = "Item3"
    Range("A1").Activate
    For I = 1 To WorksheetFunction.CountA(Columns("A:A"))
        ComboBox1.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
        ComboBox2.AddItem ActiveCell.Offset(I - 1, 0) & "-" & ActiveCell.Offset(I - 1, 1)
    Next I
'fill the collection
    For varNLoops = 0 To ComboBox2.ListCount - 1
        varCollection.Add ComboBox2.List(varNLoops)
    Next
   
End Sub


Private Sub ComboBox1_Click()
'clear combobox
    ComboBox2.Clear
'fill combobox from collection
    For varNLoops = 1 To varCollection.Count
        ComboBox2.AddItem varCollection.Item(varNLoops)
    Next
'remove item
    For varNLoops = 0 To varCollection.Count
        If ComboBox1.Value = ComboBox2.List(varNLoops) Then
            ComboBox2.RemoveItem varNLoops
            Exit Sub
        End If
    Next
   
End Sub

Hi Excel Max

It's now working, thanks a lot for your help!

 

[ana1991]

You might use code like

Private Sub ComboBox1_Click()
    ComboBox2.List = ComboBox1.List
    ComboBox2.RemoveItem ComboBox1.ListIndex
End Sub

Hi Mikerickson

Thanks a lot for your help, you made it simplier than I tough it would be possible!