Userform combobox to stop if duplicate (cobobox text)

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
51
Hello Wizards,

I thank you for any future help in advance. I have a userform with multiple comboboxes and some of them are used for the same purpose. By that I mean these comboboxes use the same dynamical named range as rowsource. I want to stop the user from duplicating a combobox twice, so I'm looking to loop through each comboboxes with the same name prefix (like: cb_01, cb_02...) and if the value/text of the active combobox already used in another, it will give an warning message and clear the active cbox. I'm thinking to put the procedure onto the exit event because I dont want it to check whilst typing into the active combobox. I hope it makes sense.

Please help me...

Thanks
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,605
Office Version
365
Platform
Windows
Modify to suit your own needs

Code:
Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub
Private Sub cb3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub Dupes()
    Dim arr As Variant, cb As Variant, cBox As Variant
    arr = Array(cb1, cb2, cb3)
    For Each cb In arr
        For Each cBox In arr
            If cb.Name <> cBox.Name Then
                If cb <> "" And cb.Value = cBox.Value Then
                    MsgBox cb.Value & vbCr & "deleted", vbCritical, "Duplicates not permitted"
                    cBox.Value = ""
                    Exit Sub
                End If
            End If
        Next cBox
    Next cb
End Sub
 
Last edited:

Sabotage

Board Regular
Joined
Sep 19, 2013
Messages
51
Yongle,

Dunno why but I could not reply to your solution multiple times. I hope this will work now with quoting you. Your code works like a charm! Thank you ever so much!

Modify to suit your own needs

Code:
Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub
Private Sub cb3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub Dupes()
    Dim arr As Variant, cb As Variant, cBox As Variant
    arr = Array(cb1, cb2, cb3)
    For Each cb In arr
        For Each cBox In arr
            If cb.Name <> cBox.Name Then
                If cb <> "" And cb.Value = cBox.Value Then
                    MsgBox cb.Value & vbCr & "deleted", vbCritical, "Duplicates not permitted"
                    cBox.Value = ""
                    Exit Sub
                End If
            End If
        Next cBox
    Next cb
End Sub
 
Last edited:

Forum statistics

Threads
1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top