none
Data validation not triggering RRS feed

  • Question

  • Hi,

    Why is data validation is not triggered when an activeX control (combobox) is putting data in a cell?

    Range A1:A15 has DV: =COUNTIF($A$1:$A$15;A1)=1

    DV is working when I input manually a duplicate value.

    On the same sheet, I have also 3 comboboxes with linked cell A1, A2, & A3. The fillrange is the same for the 3 combo's.

    DV is not triggered when a duplicate value in one of the cells is set by the combobox! Is this a know issue and is there a work arround? Thanks.

    Wkr,

    JP

    Tuesday, August 13, 2019 2:01 PM

Answers

  • You may adopt the following workaround.

    Assuming your ComboBox1 is linked to the cell A1 then place following codes on Sheet Module

    'This declaration should be the Module level declaration i.e. on the top of the Sheet Module
    Dim oVal As Variant
    
    Private Sub ComboBox1_Change()
    MsgBox Evaluate("COUNTIF($A$1:$A$15,A1)")
    If Evaluate("COUNTIF($A$1:$A$15,A1)") > 1 Then
        MsgBox "Duplicate value in the range A1:A15 is not allowed.", vbExclamation
        Range("A1").Value = oVal
    End If
    End Sub
    
    Private Sub ComboBox1_Click()
    oVal = Range("A1").Value
    End Sub
    

    Have the similar setup for the other two ComboBoxes.


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by JP Ronse Wednesday, August 14, 2019 11:06 AM
    Tuesday, August 13, 2019 11:21 PM

All replies

  • This problem has been reported before. Populating a cell using VBA or from a combo box does not trigger data validation. You could use data validation dropdowns in A1 to A3, and use the Worksheet_Change event procedure to check for duplicates:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A1:A15"), Target) Is Nothing Then
            If Evaluate("MAX(COUNTIF(A1:A15,A1:A15))") > 1 Then
                Application.EnableEvents = False
                Application.Undo
                Application.EnableEvents = True
                MsgBox "Duplicates not allowed!", vbExclamation
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 13, 2019 3:21 PM
  • You may adopt the following workaround.

    Assuming your ComboBox1 is linked to the cell A1 then place following codes on Sheet Module

    'This declaration should be the Module level declaration i.e. on the top of the Sheet Module
    Dim oVal As Variant
    
    Private Sub ComboBox1_Change()
    MsgBox Evaluate("COUNTIF($A$1:$A$15,A1)")
    If Evaluate("COUNTIF($A$1:$A$15,A1)") > 1 Then
        MsgBox "Duplicate value in the range A1:A15 is not allowed.", vbExclamation
        Range("A1").Value = oVal
    End If
    End Sub
    
    Private Sub ComboBox1_Click()
    oVal = Range("A1").Value
    End Sub
    

    Have the similar setup for the other two ComboBoxes.


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by JP Ronse Wednesday, August 14, 2019 11:06 AM
    Tuesday, August 13, 2019 11:21 PM
  • Hi Hans,

    Thanksfor the swift reply but it seems that the change event is not triggered this way.

    Wkr,

    JP

    Wednesday, August 14, 2019 11:04 AM
  • Hi Subodh,

    I had to modify your proposal a bit but is working now. Thanks.

    Wkr,

    JP

    Wednesday, August 14, 2019 11:05 AM
  • You're welcome jP! Glad it worked as desired.

    Subodh Tiwari (Neeraj) sktneer

    Wednesday, August 14, 2019 11:09 AM
  • I meant that you should do away with the combo boxes, and use data validation dropdowns instead. the Worksheet_Change code replaces the data validation rule that checks for duplicates.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 14, 2019 11:14 AM