Mobile Number Validation
VBA

VBA: User form Validation for Mobile Number input

While create the Data Entry form in VBA, We need to put some validation in text boxes and combo boxes. In this article you will how we can put the validation for Mobile Number entry.

Below is the user form wherein we have created a text box to input the Mobile number.

Mobile Number Validation
User form

After submitting, the mobile number will be added to the worksheet.

Worksheet
Worksheet

 

If you will try to enter number Non – Numeric character inside this text box then it will show below given message.

Message
Message

 

If you will try to enter more than 10 numbers inside the text box then it will show below given message.

Message
Message

While submitting a mobile number which have less than 10 number then it will show below given message.

 

Below is the code for Text box change event-

Private Sub TextBox1_Change()

Dim i, text_count As Integer

If Len(Me.TextBox1) > 0 Then

text_count = 0
For i = 1 To Len(Me.TextBox1.Value)

If IsNumeric(Mid(Me.TextBox1.Value, i, 1)) = False Then
Me.TextBox1.Value = Replace(Me.TextBox1.Value, Mid(Me.TextBox1.Value, i, 1), "")
text_count = text_count + 1
End If

Next i

If text_count > 0 Then
MsgBox "Only numbers are allowed"
Exit Sub
End If


If Len(Me.TextBox1.Value) > 10 Then
Me.TextBox1.Value = Left(Me.TextBox1.Value, 10)
MsgBox "Only 10 digits are allowed"
End If

End If


End Sub

Below is the code for Submit button.

Private Sub CommandButton1_Click()

If IsNumeric(Me.TextBox1.Value) = False Then
MsgBox "Incorrect Mobile Number"
Exit Sub
End If

If Len(Me.TextBox1.Value) < 10 Then
MsgBox "Incomplate Mobile Number"
Exit Sub
End If

Dim lr As Integer
lr = Application.CountA(ActiveSheet.Range("A:A"))

ActiveSheet.Unprotect "1234"
ActiveSheet.Range("A" & lr + 1).Value = Me.TextBox1.Value
ActiveSheet.Protect "1234"

Me.TextBox1.Value = ""

End Sub

Click here to download this Excel workbook.

 

Watch the step by step tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *