Data validation with 3 Criteria's already have data form

[vsaenz]

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]

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.

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.
____________________________________

 

[DanteAmor]

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.

 

[vsaenz]

Ehhh, no, if you want I can provide you with the code and you can try it.

thank you so much that would be fantastic!

 

[vsaenz]

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]

Let's start with the following.

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

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]

Let's start with the following.

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

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]

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]

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]

.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]

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.

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.