Formatting a textbox to accept specific characters

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hello house. Pls I need assistance on this issue....
I have a phone number textbox which is formatted to accept on numerical values of upto 11 characters
Problem:- I want the phone number format to always start with either 070, 080, or 090 followed by the rest of the 8 digits that can be any value.
Request:- pls how do I make these first 3 characters of the textbox to accept only these digits. Bear in mind the 1st and 3rd characters must only be 0 while the 2nd varies from 7 to 9. Thanks.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
141
Office Version
  1. 2010
Platform
  1. Windows
Hi,
Try.
VBA Code:
    If Not TextBox1.Text Like "0[7-9]0########" Then MsgBox "wrong format"
 

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi,
Try.
VBA Code:
    If Not TextBox1.Text Like "0[7-9]0########" Then MsgBox "wrong format"
Ok but I don't want a message box. I want the numbers not to type in just like you can't type text but only numericals. I need the text box to accept only these characters. Thanks.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
Hi, @Nour
Try this:
I borrow @veyselemre code in post 2.
VBA Code:
Private Sub TextBox1_Change()
Dim x As Long
Dim tx As String
tx = "0[7-9]0########"
    With TextBox1

        x = Len(.Text)
        If x = 1 Then
            If .Text <> "0" Then .Text = ""
        ElseIf x > 1 Then
            If Not .Text Like Left(tx, x + 4) Then .Text = Left(.Text, x - 1)
        End If
    
    End With
End Sub
 

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Thanks Akuini. I will try it and get back to you.
Hi, @Nour
Try this:
I borrow @veyselemre code in post 2.
VBA Code:
Private Sub TextBox1_Change()
Dim x As Long
Dim tx As String
tx = "0[7-9]0########"
    With TextBox1

        x = Len(.Text)
        If x = 1 Then
            If .Text <> "0" Then .Text = ""
        ElseIf x > 1 Then
            If Not .Text Like Left(tx, x + 4) Then .Text = Left(.Text, x - 1)
        End If
   
    End With
End Sub
 

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Worked exactly as intended. Thank you for the help guys. I really appreciate.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
141
Office Version
  1. 2010
Platform
  1. Windows
be an alternative,
VBA Code:
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With TextBox1
        If Len(.Text) > 11 Then .Text = Left(.Text, 11)
        If Not .Text & Right("07012345678", 11 - Len(.Text)) Like "0[7-9]0########" Then _
           .Text = Left(.Text, Len(.Text) - IIf(KeyCode = 8 Or KeyCode = 46, 0, 1))
    End With
End Sub
 

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Thank you veiselemre for the response also. However I think I will stick to the solution by Akuini as it runs in the change event. Great solutions.
 

Nour

New Member
Joined
Jun 16, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
@Akuini, pls can you break the code down for me so that I can be able to manipulate it? Just realized I still need to modify as the 3rd digit could be 0 or 1 or..080########, 081########, 090########, 091########, 070########, 071########. Also I might deploy it to a textbox for payment and receipt numbers. So how do I modify each of the character so that the first, second and third may be ranges say (0-1, 7-9, 0-2) etc. Thank you.
 

Forum statistics

Threads
1,176,138
Messages
5,901,571
Members
434,906
Latest member
Prabhu_Churi

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