Data validation with 3 Criteria's already have data form

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I have been working on this for a bit now...watched videos on all of these type of criteria but i cant seem to find one with all three.
I have a database with contacts, basic name, phone number date called and notes. What I'm trying to do is avoid duplicates when the data entry is
being inputted by multiple users of course. I understand the COUNTIF formula, and the fact that there are millions of rows so I cant put unlimited rows so I added an end range.
What I'm having trouble with is the whole number formula, with the data validation custom I add the AND then I get stuck with the LEN formula...because I input no more than
10 characters for a phone number. However, I don't like the row to just be numbers I want it to have dashes. I originally thought that if I eliminate all the dashes first then add the formula...then I could add the column format numbers but then what happened was is showed up as error because there were more than 10 characters.

Sorry if i am all over the place so let me make it clear what I'm trying to do
1. COUNTIF($C$2:$C$63,C2)=1
2. LEN(C2)<10
together do i input the equals sign first then the AND? then add both formulas like so....
=ANDCOUNTIF($C$2:$C$63,C2)=1,LEN(C2)<10

So I've tried this and i get errors on all rows i removed the LEN and get no errors but i need the limit of characters to avoid adding more numbers to the cell.
My very last question is i tried checking the COUNTIF formula and it works by itself but only until the 63 row...it lets me add duplicate past that row that's why i need it to be a future number like 999999 but it gives me an error when i do that.

Help me please...i already have a crm for other types of things but this needs to be very basic...entry data available for my agent to log in and input new numbers and call those that are on their and add notes.
I don't understand macros or vba but I've looked into them and it just seems like it gives me more than I need. I'm just looking for something simple. Yet i know I'm making it complicated with this one column i need 3 validation for.

So to be clear the 3 criteria is to continue this with additional rows being inputted no limit.
THANK YOU SO MUCH!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,752
Office Version
  1. 2007
Platform
  1. Windows
I can provide you with the code
Let's start with the following.

Put all of the following code in the events of the sheet.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  Dim n As Long, i As Long
  
  On Error GoTo appEnableEvents
  Set rng = Intersect(Target, Range("C2:C" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value <> "" Then
        Select Case True
          Case Not IsNumeric(c.Value)
            Call errPhone(c, "The phone is not numeric")
          Case Len(c.Value) <> 10
            Call errPhone(c, "The phone must be 10 digits")
          Case WorksheetFunction.CountIf(Range("C:C"), c.Value) > 1
            Call errPhone(c, "The phone already exists")
          Case IsNumeric(c.Value)
            For i = 1 To Len(c.Value)
              If Mid(c.Value, i, 1) Like "*[!0-9]*" Then
                Call errPhone(c, "The phone is not correct")
                Exit For
              End If
            Next
        End Select
      End If
    Next
  End If
  
appEnableEvents:
  Application.EnableEvents = True
End Sub

Sub errPhone(c, msg)
  Application.EnableEvents = False
  MsgBox msg, vbCritical, "Error on the phone"
  c.Value = ""
  c.Select
  Application.EnableEvents = True
End Sub

____________________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
____________________________________
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,752
Office Version
  1. 2007
Platform
  1. Windows
that's hilarious! I will have to pay someone to do that for me
I suppose you have had bad experiences, but in this forum we are helping in exchange for a thank you and smile.

I don't understand macros or vba but I've looked into them and it just seems like it gives me more than I need. I'm just looking for something simple.
My comment was to the effect that maybe you didn't want the VBA.
But I already gave you the code for you to test.
I have more than 4 thousand helps in this forum and more than 10,000 macros in todoexpertos.com and I have never asked for anything in return, I think you mistook me for someone else.
;)
 
Last edited:

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I suppose you have had bad experiences, but in this forum we are helping in exchange for a thank you and smile.


My comment was to the effect that maybe you didn't want the VBA.
But I already gave you the code for you to test.
I have more than 4 thousand helps in this forum and more than 10,000 macros in todoexpertos.com and I have never asked for anything in return, I think you mistook me for someone else.
;)
Oh emm geee that is soooo wonderful! I most definitely had bad experiences in the past....im glad this forum works this way...ill give you thank you's and smiles all day long for some good help😁
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Let's start with the following.

Put all of the following code in the events of the sheet.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  Dim n As Long, i As Long
 
  On Error GoTo appEnableEvents
  Set rng = Intersect(Target, Range("C2:C" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value <> "" Then
        Select Case True
          Case Not IsNumeric(c.Value)
            Call errPhone(c, "The phone is not numeric")
          Case Len(c.Value) <> 10
            Call errPhone(c, "The phone must be 10 digits")
          Case WorksheetFunction.CountIf(Range("C:C"), c.Value) > 1
            Call errPhone(c, "The phone already exists")
          Case IsNumeric(c.Value)
            For i = 1 To Len(c.Value)
              If Mid(c.Value, i, 1) Like "*[!0-9]*" Then
                Call errPhone(c, "The phone is not correct")
                Exit For
              End If
            Next
        End Select
      End If
    Next
  End If
 
appEnableEvents:
  Application.EnableEvents = True
End Sub

Sub errPhone(c, msg)
  Application.EnableEvents = False
  MsgBox msg, vbCritical, "Error on the phone"
  c.Value = ""
  c.Select
  Application.EnableEvents = True
End Sub

____________________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
____________________________________
omg...okay give me a bit...i want to do this soo bad...but your formula just gave me anxiety lol because its sooo much...its like i want to learn what it all means but
i know i dont need to let me just trust and follow your directions.
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Let's start with the following.

Put all of the following code in the events of the sheet.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  Dim n As Long, i As Long
 
  On Error GoTo appEnableEvents
  Set rng = Intersect(Target, Range("C2:C" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value <> "" Then
        Select Case True
          Case Not IsNumeric(c.Value)
            Call errPhone(c, "The phone is not numeric")
          Case Len(c.Value) <> 10
            Call errPhone(c, "The phone must be 10 digits")
          Case WorksheetFunction.CountIf(Range("C:C"), c.Value) > 1
            Call errPhone(c, "The phone already exists")
          Case IsNumeric(c.Value)
            For i = 1 To Len(c.Value)
              If Mid(c.Value, i, 1) Like "*[!0-9]*" Then
                Call errPhone(c, "The phone is not correct")
                Exit For
              End If
            Next
        End Select
      End If
    Next
  End If
 
appEnableEvents:
  Application.EnableEvents = True
End Sub

Sub errPhone(c, msg)
  Application.EnableEvents = False
  MsgBox msg, vbCritical, "Error on the phone"
  c.Value = ""
  c.Select
  Application.EnableEvents = True
End Sub

____________________________________
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
____________________________________
Okay i did this!! and then what?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,752
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

First, you need to remove the validations from the cells. Or work on a new sheet to test. Put the code on the new sheet.
Capture a phone in any cell in column C, the macro is activated automatically and performs the validations.
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
First, you need to remove the validations from the cells. Or work on a new sheet to test. Put the code on the new sheet.
Capture a phone in any cell in column C, the macro is activated automatically and performs the validations.
Awesome Dante...it worked i tested it...BUTTTT only if i enter in the cell going on the spreadsheet...i am trying to do it using the data entry form in excel...is there a way to enter the new rows that way and come up as an error...the data entry form populates already in a new sheet i was testing it out but it allows me to input duplicates that way. Suggestions?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,752
Office Version
  1. 2007
Platform
  1. Windows
.i am trying to do it using the data entry form in excel
What do you mean with that.

You can explain it with pictures.

I'm trying to understand. On a Sheet-X, in a certain Cell-P you capture the phone, then with a macro you copy that Cell-P and paste it on the destination sheet-Y?
So the validation should only be in Cell-P.

I am trying to guess, because that of the form and the validations you should explain them from the beginning, that would save us time.
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
What do you mean with that.

You can explain it with pictures.

I'm trying to understand. On a Sheet-X, in a certain Cell-P you capture the phone, then with a macro you copy that Cell-P and paste it on the destination sheet-Y?
So the validation should only be in Cell-P.

I am trying to guess, because that of the form and the validations you should explain them from the beginning, that would save us time.
1627948006876.png


Yes I understand what you mean. Sorry this IT stuff is just not my thang...lol.
So in this picture it shows how I input my phone numbers with a excel form that i click on that square above the tab page layout.
 

Forum statistics

Threads
1,143,687
Messages
5,720,296
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top