vba macro to restrict mobile number entry in a cell range

[Prasad K]

I want a vba macro to restrict mobile number entry in a cell range like if I type 10 digits mobile number in a cell range of column B & it will accept the 10 digit mobile number entry

If I type less than or greater than 10 digits number in a cell range then it will show me a message with popup window

MsgBox (Please check the number you have Enter The Mobile should be contain 10 Digits only)

And one more if I enter same mobile number in a cell range then it will show me a message with popup MsgBox (You have Entered the Mobile Number is Already Exist in cell B4 , you want to continue with Duplicate Mobile Number click YES (or) want to remove Duplicate Mobile Number in EnireRow click NO)

Book1
	A	B
1	Customer Name	Customer Mobile Number
2	Ajay Kumar	8521479630
3	Arun Prasad	2587413694
4	Vikram Shah	6222017896
5	Govind Patel	6321478952
6	Ashok Kumar	5467983120
7	Sujatha Reddy	5214639874
8	Aparna Kumar	7531598462
9	Arjun Prasad Rao	6899451124
10	Vikram Shah	6222017896
Sheet1

 

[Prasad K]

Any one is in online to help

Any one excel expert is online

 

[JEC]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
   If Not Intersect(Target, Range("B2:B1000")) Is Nothing And Target.Count = 1 And Len(Target) <> 10 Then
       .EnableEvents = False
        MsgBox "Please check the number you have Enter The Mobile should be contain 10 Digits only"
       .Undo
       .EnableEvents = True
       Exit Sub
   Else
       a = Application.Match(Target.Value, Range("B2:B1000"), 0)
       If IsNumeric(a) Then
           If MsgBox("You have Entered the Mobile Number is Already Exist in cell " & Cells(a, 2).Address(0, 0) & vbNewLine & "If you want to continue with Duplicate Mobile Number click (YES)" _
              & vbNewLine & "If want to remove Duplicate Mobile Number in EnireRow click (NO)", _
              vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate Entry") = vbNo Then
              .EnableEvents = False
              .Undo
              .EnableEvents = True
           End If
       End If
   End If
 End With
End Sub

 

[Prasad K]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
   If Not Intersect(Target, Range("B2:B1000")) Is Nothing And Target.Count = 1 And Len(Target) <> 10 Then
       .EnableEvents = False
        MsgBox "Please check the number you have Enter The Mobile should be contain 10 Digits only"
       .Undo
       .EnableEvents = True
       Exit Sub
   Else
       a = Application.Match(Target.Value, Range("B2:B1000"), 0)
       If IsNumeric(a) Then
           If MsgBox("You have Entered the Mobile Number is Already Exist in cell " & Cells(a, 2).Address(0, 0) & vbNewLine & "If you want to continue with Duplicate Mobile Number click (YES)" _
              & vbNewLine & "If want to remove Duplicate Mobile Number in EnireRow click (NO)", _
              vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate Entry") = vbNo Then
              .EnableEvents = False
              .Undo
              .EnableEvents = True
           End If
       End If
   End If
 End With
End Sub

it's working and here is a problem
if i type same mobile number in that cell range then it's not removing in entire row & one more thing if i type same mobile number in cell range then it will showing different cell value address in MsgBox
i am attaching screen shot please see

 

[JEC]

The entirerow thing will be tomorrow, the right cell:

change it to: Cell(a+1,2).Address

 

[Prasad K]

change it to: Cell(a+1,2).Address

Not working
ok then ignore my question i have solved i have changed the code little bit

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
 On Error Resume Next
   If Not Intersect(Target, Range("B1:B1000")) Is Nothing And Target.Count = 1 And Len(Target) <> 10 Then
       .EnableEvents = False
        MsgBox "Please check the number you have Enter The Mobile should be contain 10 Digits only"
       .Undo
       .EnableEvents = True
       Exit Sub
   Else
       a = Application.Match(Target.Value, Range("B1:B1000"), 0)
       If IsNumeric(a) Then
           If MsgBox("You have Entered the Mobile Number is Already Exist in cell " & Cells(a, 2).Address(0, 0) & vbNewLine & "If you want to continue with Duplicate Mobile Number click (YES)" _
              & vbNewLine & "If want to remove Duplicate Mobile Number in EnireRow click (NO)", _
              vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate Entry") = vbNo Then
              Target.Cells.EntireRow.Delete
              .EnableEvents = False
              .Undo
              .EnableEvents = True
           End If
       End If
   End If
 End With
End Sub