do not allow select same answer in multiple comboboxes 1

[CookEy]

Hi All

I have searched for a number of days and have not been able to find an answer to my question.
(out of courteously i will also point out i have posted this question on another board without however there has been no answer. i havent posted a link to the thread as i do not know the etiquette)

I have a form with multiple combo boxes on it. The combo boxes have the same list of data in them. The user can also type there own data into these combo boxes. I want to stop the user from being able to make the same selection in any of the multiple combo boxes. The combo boxes are created at run time and can vary in number.

So what I have done so far is added them to a class module and also a collection. I have added them to a class module so that i can utilize the change event. Once this change event fires it adds the combobox.value to another collection. It then cycles through all the results in the collection and checks that it hasn’t been duplicated. If it has it changes the .ControlTipText = "dup". My plan was then to check all the control tips before exiting the form for the tag "dup" and if it exists show a message to the user.

The code In the form module(setUpWizard) i have

Dim BasisFeeName() As New FormControl
Private pCombo2boxes As New Collection
 
Public Property Get Combo2boxes() As Collection
      Set Combo2boxes = pCombo2boxes
End Property
 
Private Sub CreatePage4()
 
Dim R As Integer, CtlCount4 As Integer
Dim NewCombo As msforms.ComboBox
Dim rCell As Range, rData As Range
 
With Worksheets("Fund Fees")
       Set rData = Range(.Range("B1"), .Range("IV1").End(xlToLeft))
End With
 
CtlCount4 = 0
 
For R = 1 To 2
CtlCount4 = CtlCount4 + 1
 
 
 
Set NewCombo = Me.Controls.Add("Forms.ComboBox.1", "BasisFeeName" & R, True)
With NewCombo
        .Top = (18 - 1) * (R - 1) + 18
        .Left = 70
        .Width = 78
        .Height = 15
        .Style = 0
End With
 
For Each rCell In rData
     With rCell
            If .MergeCells Then
                   If rCell.Value <> "" Then
                             NewCombo.AddItem rCell.Value
                   End If
             End If
      End With
Next
 
 
ReDim Preserve BasisFeeName(1 To CtlCount4)
Set BasisFeeName(CtlCount4).cBasisFeeName = NewCombo
BasisFeeName(CtlCount4).Combo2Name = NewCombo.Name
BasisFeeName(CtlCount4).SetCombo2Box NewCombo
pCombo2boxes.Add BasisFeeName(CtlCount4)
 
 
Next
 
End Sub

code for class module called “FormControl”

Public WithEvents cBasisFeeName As msforms.ComboBox
 
Private pCombo2Numbers As Collection
Private pCombo2Name As String
 
Public Property Let Combo2Name(Value As String)
    pCombo2Name = Value
End Property
 
Public Property Get Combo2Name() As String
    Combo2Name = pCombo2Name
End Property
 
Public Property Get Combo2Numbers() As Collection
    Set Combo2Numbers = pCombo2Numbers
End Property
 
Public Sub SetCombo2Box(Value As msforms.ComboBox)
    Set BasisFeeName = Value
End Sub
 
Private Sub cBasisFeeName_change()
    Dim v As String, l As Long
 
    With cBasisFeeName
        If Len(.Text) = 0 Then
            .ControlTipText = ""
        Else
            'store all the values in the comboboxs
            Set pCombo2Numbers = New Collection
            
            pCombo2Numbers.Add .Value
            'check for duplicates
           
                If Check2Values Then
               
                    .ControlTipText = "dup"
               
                Else
                
                    .ControlTipText = ""
                
                End If
            
        End If
    End With
End Sub
Private Function Check2Values() As Boolean
    Dim n As Long, l As Long
     
    For n = 1 To pCombo2Numbers.Count
        For Each BasisFeeName In SetupWizard.Combo2boxes
            If Not BasisFeeName.Combo2Numbers Is Nothing Then
                For l = 1 To BasisFeeName.Combo2Numbers.Count
                    If BasisFeeName.Combo2Numbers(l) = pCombo2Numbers(n) Then
                        If Not (BasisFeeName.Combo2Name = pCombo2Name And l = n) Then
                            Check2Values = True
                            Exit Function
                        End If
                    End If
                Next l
            End If
        Next BasisFeeName
    Next n
 End Function

My problem is that when I make the same selection in combobox no.2 as in combobox no.1. my code rightly adds the controltip “dup” to combobox no.2.

if I change combobox no.2 value then it removes the controltip however if I change combobox no.1 instead, combobox no.2 still keeps the controltip.

Any ideas how I can solve this problem?

 

[SkipVought]

Hi,

What application, please?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CookEy]

apologies this is for excel 97

 

[Andrzejek]

This is a 'work in progress' and the blue code needs to be 'adjusted', but this can give you an idea on where I would go with it....:

Dim cntr As Control
Dim aryText() As String
Dim i As Integer
Dim blnFoundOnce As Boolean

i = 1

For Each cntr In Me.Controls
    If TypeOf cntr Is ComboBox Then
        If cntr.Text <> "" Then
            ReDim Preserve aryText(i)
            aryText(i) = cntr.Text
            i = i + 1
        End If
    End If
Next cntr
[green]
'For i = 1 To UBound(aryText)
'    Debug.Print i & " - " & aryText(i)
'Next i[/green]

For Each cntr In Me.Controls
    If TypeOf cntr Is ComboBox Then[blue]
        For i = 1 To UBound(aryText)
            If cntr.Text = aryText(i) And blnFoundOnce = True Then
                MsgBox "You have this text twice"
                Exit For
            End If
            If cntr.Text = aryText(i) Then
                blnFoundOnce = True
            End If
        Next i[/blue]
    End If
Next cntr

Have fun.

---- Andy

 

[CookEy]

Thanks for your response Andy.

im unsure how to implement your technique. as mentioned my combo boxes are added at run time. ( my employer has prohibatied certain aspects of macro security) i am unable to vbe program adding the code at run time hence my approach of adding them to a class module. so the code is already writen for the event.

looking at your example i have a question the combo boxs i wish to run the code on are not the only combo box's on the page. how could i only isolate the combo box's that i need the code run on.

 

[CookEy]

sorry i didnt explain myself properly. of course i understand i could use an if cntr.name = relevent combo box name. statement.

what i meant was how could i do this without having to loop through the unneccesary controls. The reason i ask is because i may have anything between 5 and one hundred combo box' on the form looping through these would be quite processor intensive.

 

[SkipVought]

I would put the list on a sheet, with a heading.

Use the list as the list fill range for th combobox.

The combobox1 selection will be the criteria for a QUERY against the list and the resultset will be the list fill range for the next combobox, and so on...

Dim sSQL as string
sSQL = "Select [List1Heading] "
sSQL = sSQL & "From [List1Sheet$] "
sSQL = sSQL & "Where [List1Heading] <> '" & Combobox1.Text & "'"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

On the second thought....

Since the combo boxes can show either an item from the list or user can type anything in them, you can just compare each combo to any other combo, like this:

Sub CheckForDups(frm As UserForm)
Dim cboA As Control
Dim cboB As Control

For Each cboA In frm.Controls
    If TypeOf cboA Is ComboBox Then
        For Each cboB In frm.Controls
            If TypeOf cboB Is ComboBox Then
                [blue]If cboA.Tag = "ABC" And cboB.Tag = "ABC" Then[/blue]
                    If cboA.Name <> cboB.Name Then
                        If cboA.Text = cboB.Text And _
                        cboA.Text <> "" And cboB.Text <> "" Then
                            MsgBox "You have a duplicate text"
                            Exit Sub
                        End If
                    End If
                [blue]End If[/blue]
            End If
        Next cboB
    End If
Next cboA


End Sub

You can call it from anywhere (like a Change event), just pass the UserForm the combos are on:

[tt] Call CheckForDups(Me)[/tt]

You said you may have a lot of combo boxes that you want to by-pass. When creating the combos, you may assign a Tag to a combo box and check only the combos with that Tag.

Have fun.

---- Andy

 

[CookEy]

Thanks I believe that to be an excellent solution