Prevent duplicate selections across multiple comboboxes

[Mammon]

I'm trying to create a spreadsheet where the user makes multiple selections; there will possibly be thirty or more comboboxes, each with calling up the same named range. I'm looking to prevent the user from being able to make the same selection in more than one combobox, however I'm really not sure how to go about this. (I've seen posts on preventing duplicates within a single combobox, however my list will be static and won't contain any duplicates, the issue is strictly between user choices)

Would a code have to be added to each combobox's "_change()" event, or is there a way of applying this kind of control globally? Also, I'm not using a form control because the amount of selections required seems too large to be easily used, but I'm not set against it if you've got a better idea. I'm quite new to excel and would really appreciate any help you could offer, hopefully explained in detail.

 

[MickG]

Try this:-
Add the extra Combobox codes as below
You can place the code in the Worksheet Module.

Sub Dup(Com As Object)
Dim Shp As OLEObject
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare


For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "ComboBox" Then
        If Not .Exists(Shp.Object.value) Then
            .Add Shp.Object.value, Nothing
        Else
            Com.value = ""
            MsgBox "This value already exists"
        End If
    End If
Next
End With
End Sub


Private Sub ComboBox1_Change()
Call Dup(ComboBox1)
End Sub


Private Sub ComboBox2_Change()
Call Dup(ComboBox2)
End Sub


Private Sub ComboBox3_Change()
Call Dup(ComboBox3)
End Sub


Private Sub ComboBox4_Change()
Call Dup(ComboBox4)
End Sub

 

[Mammon]

I really appreciate the quick response. Unfortunately I'm getting the "this value already exists" message every time. I have the comboboxes load via the following:

Sub FillCombobox1()
Dim el As Range, r1 As Range
Set r1 = Sheets("sheet2").Range("List1")
For Each el In r1
Worksheets("sheet1").ComboBox1.AddItem el
Next

End Sub

I have it call that function on opening the workbook so that everything is preloaded for the user. I also have a function that, when opening the workbook, sets it to the default index 0 position (which is the word "none"). I understand that this would conflict with the "no duplicates" clause but I tried just disabling the "set default index value" function and still had the issue. Would either of these functions be causing the problem?

As it stands I cannot select items without the "already exists" message coming up and then each selection is blanked out. Any further thoughts?

 

[Mammon]

Sorry for the double post but I just realized that after saving and closing the file, on reopening the "already exists" message pops up more times per click than before, so I think that the value is being registered multiple times. If I disable the sub dup code (and the ones to call it), save, then reload and reactivate it everything works as it should, but only for that one instance. If I then save and reload again the same problem persists.

Is it possible to start with a blank slate when opening the workbook? I'm hoping to create a save macro attached to a button later which will allow the user's filled-out form to be saved seperately, but I'd like the main file to be reusable. I'm really not familiar with library objects so I'm not sure how to go about wiping the slate clean so to speak.

 

[MickG]

Its because the code keeps setting of the "Change _Event". Are you able to load your data with a "ListFillRange" in the Comboboxes Properties Box.

 

[Mammon]

Yeah, that worked perfectly. Why was I making things more complicated than it needed to be? Thanks so much!

 

[MickG]

You're welcome
Regrds Mick