Check one Combobox value against another Combobox value

[Pacman52]

Hi all,

So I've just been testing a userform thats nearly finished (thankfully!) and came across a problem.

So on the form I have 8 comboboxes to enter products i.e. cmbProd1, cmbProd2 and so on. whilst testing I noticed that a user can select a product item from the list more than once - basically they could enter the same product in each of the 8 boxes.

Is there a way to check if a product has already been selected if a user has already selected it in another combobox? if so how would I code this and what would be the best Sub to do the check in?

The 'flow' of the form means that a user can't move on to the next or subsequent combobox unless the preceeding box has had something selected,

It may be worth mentioning that my knowledge on comboboxes is very basic to say the least so please bear that in mind with any replies.

Thanks very much Paul

 

[NoSparks]

think you should post the code that loads the combo drop downs

 

[Pacman52]

think you should post the code that loads the combo drop downs

Hi NoSpark thanks for the reply code used is:

Private Sub InitializeControls()

    textboxPayMeth.Enabled = False
    textboxPayMeth.Visible = False
    
    Me.ComboProd1.List = GetProducts()
    Me.ComboProd1.ListIndex = 0
    Me.ComboProd2.List = GetProducts()
    Me.ComboProd2.ListIndex = 0
    Me.ComboProd3.List = GetProducts()
    Me.ComboProd3.ListIndex = 0
    Me.ComboProd4.List = GetProducts()
    Me.ComboProd4.ListIndex = 0
    Me.ComboProd5.List = GetProducts()
    Me.ComboProd5.ListIndex = 0
    Me.ComboProd6.List = GetProducts()
    Me.ComboProd6.ListIndex = 0
    Me.ComboProd7.List = GetProducts()
    Me.ComboProd7.ListIndex = 0
    Me.ComboProd8.List = GetProducts()
    Me.ComboProd8.ListIndex = 0

End Sub

 

[NoSparks]

This isn't exactly what you asked but might be usable.

Only load the first combobox drop down at initialization and have the other 7 disabled.
Have the change event of a combo enable the next combo
Use the enter event to populate that next combo from the list of the prior combo
and remove the prior combos selection

for example:

Private Sub ComboBox1_Change()
    Me.ComboBox2.Enabled = True
End Sub

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

Private Sub ComboBox2_Change()
    Me.ComboBox3.Enabled = True
End Sub

Private Sub ComboBox3_Enter()
    Me.ComboBox3.List = Me.ComboBox2.List
    Me.ComboBox3.RemoveItem (Me.ComboBox2.ListIndex)
End Sub
'''''and so on'''''

I suspect something would need to be implemented to prevent the user from going back and changing a previous selection.
Anyway, just an idea that may or may not be of assistance.

 

[Pacman52]

Hi thanks so much for the suggestion. I can see why and how this would work and may well just end up using this method. Ideally though I wanted some form of prompt to the user to let them know a product had already been selected in another box but in reality there is only 8 boxes so maybe I'm being a bit over the top.

I'll try what you've suggested tomorrow and let you know how it works out.

Paul

 

[NoSparks]

but in reality there is only 8 boxes so maybe I'm being a bit over the top.

especially if the user is sitting there looking at them

 

[Pacman52]

Just to let you know as expected that work fine - not ideal as mentioned before but if a user can't work out why a product isn't in a different box after selecting it already then they really shouldn't be using a PC lol

Thanks again for the help.

Paul