data validation non contiguous cells

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
Hello I have non contiguous range ($U$2:$AS$1000 and $AW$2:$BF$1000) I need a data validation to alert users when the same date is entered in both range combine more than 12 time.


Thanks
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
I am not sure how you would do that with Data Validation, but it can be done with VBA. We can use a Worksheet_Change event procedure, which is VBA code that will run autoamtically, as manual entries are made, one cell at a time.

So, to use the following code, go to the sheet you would like to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range, rng2 As Range
    
'   Set two ranges to check
    Set rng1 = Range("$U$2:$AS$1000")
    Set rng2 = Range("$AW$2:$BF$1000")

'   Exit sub if more than one cell is updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit sub if updated cells does not fall in one of our ranges
    If (Intersect(Target, rng1) Is Nothing) And (Intersect(Target, rng2) Is Nothing) Then Exit Sub
    
'   See how many times entry appears in range
    If IsDate(Target) Then
        If Application.WorksheetFunction.CountIf(rng1, Target.Value) + _
            Application.WorksheetFunction.CountIf(rng2, Target.Value) > 12 Then
            MsgBox "That date appears more than 12 times!"
        End If
    End If
    
End Sub
So, if the date you entered into those ranges already exists in them 12 or more times, you will get a message alerting you of that fact.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,696
Office Version
365
Platform
Windows
Hello I have non contiguous range ($U$2:$AS$1000 and $AW$2:$BF$1000) I need a data validation to alert users when the same date is entered in both range combine more than 12 time.
Data Validation (Custom formula) in U2 of ..
=COUNTIF($U$2:$AS$1000,U2)+COUNTIF($AW$2:$BF$1000,U2)<=12
.. & copied to the rest of the two ranges would prevent an entry being made more that 12 times. It will not differentiate between dates and any other type of entry though.
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
I am not sure how you would do that with Data Validation, but it can be done with VBA. We can use a Worksheet_Change event procedure, which is VBA code that will run autoamtically, as manual entries are made, one cell at a time.

So, to use the following code, go to the sheet you would like to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range, rng2 As Range
    
'   Set two ranges to check
    Set rng1 = Range("$U$2:$AS$1000")
    Set rng2 = Range("$AW$2:$BF$1000")

'   Exit sub if more than one cell is updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit sub if updated cells does not fall in one of our ranges
    If (Intersect(Target, rng1) Is Nothing) And (Intersect(Target, rng2) Is Nothing) Then Exit Sub
    
'   See how many times entry appears in range
    If IsDate(Target) Then
        If Application.WorksheetFunction.CountIf(rng1, Target.Value) + _
            Application.WorksheetFunction.CountIf(rng2, Target.Value) > 12 Then
            MsgBox "That date appears more than 12 times!"
        End If
    End If
    
End Sub
So, if the date you entered into those ranges already exists in them 12 or more times, you will get a message alerting you of that fact.
Note sure what going on. I tried the above code it does not seem to work.

I also have the following code in that sheet which allows me to double click on another range do not thing that this would interfere?

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    If Not Intersect(Target, Range("M2:O1000")) Is Nothing Then
        Application.EnableEvents = False
        If ActiveCell.Value = "X" Then
            ActiveCell.ClearContents
        Else
            ActiveCell.Value = "X"
        End If
        Cancel = True
    End If
    Application.EnableEvents = True
End Sub
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
Thanks Peter. I really need this to work for Dates as that is the only data that will be inputted in these ranges.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
I don't think that code should interfere.

You are manually inputting those dates in the range, right?
Are you entering a certain date more than 12 times?

Many turn on the Macro Recorder, and record yourself entering a situation which should trigger the code to run, then stop the Macro Recorder, and post the code here.
Then, I can try to recreate the exact thing you are doing and check to see if my code works on it.
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
You are manually inputting those dates in the range, right? YES
Are you entering a certain date more than 12 times? YES

Code:
Sub Macro1()'
' Macro1 Macro
'


'
    Range("U4").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("U11").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("U16").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("U22").Select
    ActiveCell.FormulaR1C1 = "1/12020"
    Range("U22").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("U28").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AD33").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AL36").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AL38").Select
    ActiveWindow.SmallScroll Down:=24
    ActiveWindow.SmallScroll ToRight:=24
    Range("AX43").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AX48").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AX53").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("BB60").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("BB64").Select
    ActiveWindow.SmallScroll Down:=9
    Range("AX70").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AX79").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AX84").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("BB86").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("BB89").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("BB91").Select
    ActiveWindow.SmallScroll Down:=3
    Range("AG90").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AG94").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AG97").Select
    ActiveCell.FormulaR1C1 = "1/1/2020"
    Range("AG99").Select
End Sub
so for above ex. I entered 1/1/2020 more than 12 times it did noting. I want it to give a warning when any date that is entered 12 times.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
Hi @hajiali, In Peter's validation, if you copy the value more than 12 times it does not verify. Same with Joe's code.


If you are going to capture one or several dates at once, try the following:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r1 As Range, r2 As Range, c As Range
  Set r1 = Range("$U$2:$AS$1000")
  Set r2 = Range("$AW$2:$BF$1000")
  If Target.Count > Union(r1, r2).Count Then Exit Sub
  On Error GoTo AppEnable
  If Not Intersect(Target, Union(r1, r2)) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Target
      If c.Value <> "" Then
        If WorksheetFunction.CountIf(r1, c.Value) + WorksheetFunction.CountIf(r2, c.Value) > 12 Then
          MsgBox "Same date is entered more than 12 time: " & c.Value
          Target.Value = ""
        End If
    End If
    Next
    Application.EnableEvents = True
  End If
AppEnable:
Application.EnableEvents = True
End Sub
Note: Under the code you can put your DoubleClick code without problem.
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
DanteAmor thank you for this code works great. I want it just to be a warning and allow user if they want to continue Yes or No, if Yes then apply date if no than end
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
Try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r1 As Range, r2 As Range, c As Range, resp As Variant
  Set r1 = Range("$U$2:$AS$1000")
  Set r2 = Range("$AW$2:$BF$1000")
  If Target.Count > Union(r1, r2).Count Then Exit Sub
  On Error GoTo AppEnable
  If Not Intersect(Target, Union(r1, r2)) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Target
      If c.Value <> "" Then
        If WorksheetFunction.CountIf(r1, c.Value) + WorksheetFunction.CountIf(r2, c.Value) > 12 Then
          resp = MsgBox("Same date is entered more than 12 time: " & c.Value & vbCr & vbCr & _
            "Do you want to continue?", vbQuestion & vbYesNo)
          Select Case resp
            Case vbNo
              Target.Value = ""
            Case vbYes
              Exit For
          End Select
        End If
      End If
    Next
    Application.EnableEvents = True
  End If
AppEnable:
Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,369
Messages
5,468,219
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top