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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Try this:-
Nb:- The last code (My test code) ,I used to load the comboboxes, Don't use "ListFiullRange"
Code: 
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] c       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] n       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] ray()
[COLOR=navy]Dim[/COLOR] Ac      [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
c = 0
[COLOR=navy]For[/COLOR] Ac = 2 To 3
With Me.Controls("Combobox" & Ac).Object '[COLOR=green][B].Value[/B][/COLOR]
[COLOR=navy]For[/COLOR] n = 0 To .ListCount - 1
    [COLOR=navy]If[/COLOR] Not .List(n) = ComboBox1.List(ComboBox1.ListIndex) [COLOR=navy]Then[/COLOR]
        ReDim Preserve ray(c)
            ray(c) = .List(n)
            c = c + 1
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
    .Clear
    .List = ray
     c = 0
[COLOR=navy]End[/COLOR] With
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
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")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Nb:- The last code (My test code) ,I used to load the comboboxes, Don't use "ListFiullRange"
Code: 
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] c       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] n       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] ray()
[COLOR=navy]Dim[/COLOR] Ac      [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
c = 0
[COLOR=navy]For[/COLOR] Ac = 2 To 3
With Me.Controls("Combobox" & Ac).Object '[COLOR=green][B].Value[/B][/COLOR]
[COLOR=navy]For[/COLOR] n = 0 To .ListCount - 1
    [COLOR=navy]If[/COLOR] Not .List(n) = ComboBox1.List(ComboBox1.ListIndex) [COLOR=navy]Then[/COLOR]
        ReDim Preserve ray(c)
            ray(c) = .List(n)
            c = c + 1
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
    .Clear
    .List = ray
     c = 0
[COLOR=navy]End[/COLOR] With
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
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")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

THanks, I tried your code, added 3 combos to a sheet in excel (ver 2003) named ComboBox1, 2 and 3. They populated just fine but I see error: "Method or Data member not found" against me.controls... when I change combo 1

Is this because I've got the 3 combos on a sheet rather than a userform?
 
Last edited:
Upvote 0
Change this line:-
Rich (BB code): 
For Ac = 2 To 3
With Me.Controls("Combobox" & Ac).Object
To
Rich (BB code): 
For Ac = 2 To 3
With Activesheet.OLEObjects("Combobox" & Ac).Object
Mick
 
Upvote 0
MickG said:
Change this line:-
Rich (BB code): 
For Ac = 2 To 3
With Me.Controls("Combobox" & Ac).Object
To
Rich (BB code): 
For Ac = 2 To 3
With Activesheet.OLEObjects("Combobox" & Ac).Object
Mick
Click to expand...

Thanks again, this does work to a degree.

Problem with users is that they change their mind and might change their selection in the combo box. So using combo 1 as the the example, they might initially select item A which subsequently gets removed from the other combos as planned, but then they decide to select item D. That also gets removed for the other two combos, but item A is also still missing, but it should come back.

I think this is probably do-able (In fact I've done it before with dates in the combos - but that was much easier).

So, for the sake of simplicity, I'll just test for the same value in one or more combos and show the user an error message.

But thanks for the help :)
 
Upvote 0
This is perhaps what you want, I wrote this in the first place, I then decided you wanted the other code.
Hope this works for you !!!

Code: 
[COLOR="Navy"]Sub[/COLOR] MG11Jul42
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ray()
[COLOR="Navy"]With[/COLOR] ComboBox1
    [COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1
        [COLOR="Navy"]If[/COLOR] Not .List(n) = .List(.ListIndex) [COLOR="Navy"]Then[/COLOR]
            ReDim Preserve ray(c)
            ray(c) = .List(n)
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]With[/COLOR] ComboBox2
    .Clear
    .List = ray
 [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]With[/COLOR] ComboBox3
    .Clear
    .List = ray
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
This is perhaps what you want, I wrote this in the first place, I then decided you wanted the other code. Hope this works for you !!!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG11Jul42Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR][COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR][COLOR="Navy"]Dim[/COLOR] ray()[COLOR="Navy"]With[/COLOR] ComboBox1    [COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1        [COLOR="Navy"]If[/COLOR] Not .List(n) = .List(.ListIndex) [COLOR="Navy"]Then[/COLOR]            ReDim Preserve ray(c)            ray(c) = .List(n)            c = c + 1        [COLOR="Navy"]End[/COLOR] If    [COLOR="Navy"]Next[/COLOR] n [COLOR="Navy"]End[/COLOR] With [COLOR="Navy"]With[/COLOR] ComboBox2    .Clear    .List = ray [COLOR="Navy"]End[/COLOR] With [COLOR="Navy"]With[/COLOR] ComboBox3    .Clear    .List = ray [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR][COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
Click to expand...
Ahh, that work - nice one.How much work would it take to adapt this so that user could select any of the 3 combos and the selected item would be removed from the other two combos? - thanks
 
Upvote 0
This seems to work, Note you need to use the "Click Event" rather than the "Change Event"
You also need to have an your Combobox list in the code as shown.
If you load you comboboxes from the sheet "activate Event", then the code should ensure you can't select the same value from different comboboxes.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG12Jul49
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Click()
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
[COLOR="Navy"]With[/COLOR] ComboBox1
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
   [COLOR="Navy"]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR="Navy"]Then[/COLOR]
            ComboBox2.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox2_Click()
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox3.Clear
[COLOR="Navy"]With[/COLOR] ComboBox2
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
    [COLOR="Navy"]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR="Navy"]Then[/COLOR]
            ComboBox1.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Click()
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox2.Clear
[COLOR="Navy"]With[/COLOR] ComboBox3
 [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
    [COLOR="Navy"]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR="Navy"]Then[/COLOR]
            ComboBox1.AddItem Ray(n)
            ComboBox2.AddItem Ray(n)
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
This seems to work, Note you need to use the "Click Event" rather than the "Change Event"
You also need to have an your Combobox list in the code as shown.
If you load you comboboxes from the sheet "activate Event", then the code should ensure you can't select the same value from different comboboxes.
Code: 
[COLOR=navy]Sub[/COLOR] MG12Jul49
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Click()
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox2.Clear
ComboBox3.Clear
[COLOR=navy]With[/COLOR] ComboBox1
[COLOR=navy]For[/COLOR] n = 0 To UBound(Ray)
   [COLOR=navy]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR=navy]Then[/COLOR]
            ComboBox2.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
 [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox2_Click()
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox3.Clear
[COLOR=navy]With[/COLOR] ComboBox2
[COLOR=navy]For[/COLOR] n = 0 To UBound(Ray)
    [COLOR=navy]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR=navy]Then[/COLOR]
            ComboBox1.AddItem Ray(n)
            ComboBox3.AddItem Ray(n)
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] n
 [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox3_Click()
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
Ray = Array("A", "B", "C", "D", "E", "F", "G", "H")
ComboBox1.Clear
ComboBox2.Clear
[COLOR=navy]With[/COLOR] ComboBox3
 [COLOR=navy]For[/COLOR] n = 0 To UBound(Ray)
    [COLOR=navy]If[/COLOR] Not Ray(n) = .List(.ListIndex) [COLOR=navy]Then[/COLOR]
            ComboBox1.AddItem Ray(n)
            ComboBox2.AddItem Ray(n)
        [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thanks Mick, that's almost perfect.

Only thing now is that the selections in the combo boxes are lost as they reset. Items are removed okay, so when I select say, D from cbo1, D is removed from cbo2 and cbo3 which is perfect. But if I then choose E from cbo2, I lose the D selection from cbo1.
Is there a way around that?

Thanks again.
 
Upvote 0
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
 
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
792
GA036872
G
R
  • Solved
How to use variables from a user form in a macro
Replies
5
Views
846
dmt32
D
M
  • Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
502
Megg
M
P
  • Question
If selection is made from listbox remove same item from another listbox and comboboxes
Replies
0
Views
502
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
216
rgree05
R

Forum statistics

Threads
1,214,824
Messages
6,121,784
Members
449,049
Latest member
greyangel23

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