vb 3 combos containing same items - how to remove selected item form other 2 combos?

W

winwell

New Member
Joined
Mar 10, 2011
Messages
17
I have 3 combos on a excel form. Each contains identical items. When combo 1 is selected I want that item to disappear from combos 2 and 3 and so on. Have tried combo2.removeitem(combo1.ListIndex) but the lists get reindexed all the time so soon become out of sync with eachother. So option A might be selected in combo 1, but option C gets removed from the other combos so it gets messy. Ideally I would reload each combo on the change event of any of the combos, but for the other two that weren't changed omit the one that was selected in combo that changed.

Is there a way to achieve this or should I just test for a duplicate selection and throw an error if user selects the same entry in two combos?

Thanks for any help
 
Last edited:
Sort by date Sort by votes
MickG said:
Try adding the code in Red to each Combobox.
Rich (BB code): 
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
With ComboBox1
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox2.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub
Click to expand...

Yes, I 'll try that Monday as I'm away from the office now, thanks
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I tried as suggested and first selection from cbo1 works, but second selection from cbo2 throws run time 381 error: Could not get the list property. invalid property array index.

Code: 
Private Sub ComboBox1_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
With ComboBox1
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox2.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Private Sub ComboBox2_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox3.Clear
With ComboBox1
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox1.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Private Sub ComboBox3_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox2.Clear
With ComboBox1
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox1.AddItem Ray(n)
            ComboBox2.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Thanks
 
Upvote 0
Ah - just noticed my error when pasting code in - I changed to this and its working:

Rich (BB code): 
Private Sub ComboBox1_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
With ComboBox1
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox2.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Private Sub ComboBox2_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox3.Clear
With ComboBox2
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox1.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Private Sub ComboBox3_Click()
Dim Ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox2.Clear
With ComboBox3
For n = 0 To UBound(Ray)
   If Not Ray(n) = .List(.ListIndex) Then
            ComboBox1.AddItem Ray(n)
            ComboBox2.AddItem Ray(n)
        End If
    Next n
 End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub

Thanks for all the help with this - Kudos to you Mick
 
Upvote 0
I should try this on a new sheet, with new comboboxes.
The sheet activate will load the comboboxes and set the Value to blank.
It should then work.
Only when its working ok should I try and change the Combobox lists.
I believe there is a better solution, but this is the best i can do for now.
Code: 
Private Sub ComboBox1_Click()
Dim ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
With ComboBox1
For n = 0 To UBound(ray)
If Not ray(n) = .List(.ListIndex) And Not ray(n) = Temp2 And Not ray(n) = Temp3 Then
ComboBox2.AddItem ray(n)
ComboBox3.AddItem ray(n)
End If
Next n
End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub
Private Sub ComboBox2_Click()
Dim ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox3.Clear
With ComboBox2
For n = 0 To UBound(ray)
If Not ray(n) = .List(.ListIndex) And Not ray(n) = Temp1 And Not ray(n) = Temp3 Then
ComboBox1.AddItem ray(n)
ComboBox3.AddItem ray(n)
End If
Next n
End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub
Private Sub ComboBox3_Click()
Dim ray
Dim n As Integer
Dim Temp1 As String
Dim Temp2 As String
Dim Temp3 As String
Temp1 = ComboBox1.Value
Temp2 = ComboBox2.Value
Temp3 = ComboBox3.Value
ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox2.Clear
With ComboBox3
For n = 0 To UBound(ray)
If Not ray(n) = .List(.ListIndex) And Not ray(n) = Temp1 And Not ray(n) = Temp2 Then
ComboBox1.AddItem ray(n)
ComboBox2.AddItem ray(n)
End If
Next n
End With
ComboBox1.Value = Temp1
ComboBox2.Value = Temp2
ComboBox3.Value = Temp3
End Sub
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Activate()
ComboBox1.List = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.List = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox3.List = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.ListIndex = -1
ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Help with writing VBA for data entry form
Replies
2
Views
805
GA036872
G
R
  • Solved
How to use variables from a user form in a macro
Replies
5
Views
853
dmt32
D
M
  • Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
532
Megg
M
P
  • Question
If selection is made from listbox remove same item from another listbox and comboboxes
Replies
0
Views
508
Pookiemeister
P
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
229
rgree05
R

Forum statistics

Threads
1,215,417
Messages
6,124,783
Members
449,188
Latest member
Hoffk036

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top