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.

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

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

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

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

Table of Contents

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