How to Format Textbox Phone Number in VBA

So far, we mainly discussed Modules when we talked about the VBA. But, if you have ever dealt with Visual Basic in Excel, there is a high chance that you stumbled upon User Forms as well.

In the example below, we will show how to create User Forms, how to assign code to them, and how to use built-in codes in User Forms to your advantage. We will use this knowledge to format Text Box in User Form to show the phone number in a way we want.

Creating User Forms in VBA

The first thing that we need to do is to create a User Form. To do so, we need to open the Visual Basic, either by going to the Developer tab >> Code >> Macros or simply by clicking ALT + F11.

Once we are on this window, we will right-click anywhere on the left window (beneath the Project) and go to Insert >> UserForm:

Graphical user interface, application

Description automatically generated

When we click on it, our User Form will be created:

Graphical user interface

Description automatically generated

To manipulate our User Form, we need to add the Toolbox. We will click on the View tab, and then on the Toolbox:

Graphical user interface, application

Description automatically generated

When we click on it, our Toolbox will be visible to us:

Graphical user interface

Description automatically generated with low confidence

Format Text Box Phone Number with User Form

On our Toolbox, there are a bunch of useful controls. The first one that we are going to use is Text Box. It is located third in the first row. We click on it and simply add it to our User Form:

Graphical user interface, table

Description automatically generated

We will write the text “Enter Number”. Remember that this text will be the default text whenever we trigger our User Form.

For the next thing, we will rename our Text Box to be simply “Number” in the Properties window.

Graphical user interface, application, table, Excel

Description automatically generated

When we double click on our Text Box in the User Form, the following window will appear:

Graphical user interface, text, application, email

Description automatically generated

You will notice that the automatic function was created. In this particular case, the code that will work on each change in the Text Box (which is now named Number) was created, as seen in the top right corner.

However, to get what we want, i.e. to format the number in the Text Box in the exact way we want, we will use a different method, not Change, but rather Exit. You will notice that we can choose among many options on the dropdown menu:

Graphical user interface, text, application, email

Description automatically generated

You will also notice that, once we click on it, the new code is automatically being formatted, along with defining ByVal as our variable.

All we do need to do now is to input our line of code, which will be:

Private Sub Number_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Number.Text = Format(Number.Text, "000-000-0000")

And it will look like this in the window:

Text

Description automatically generated with medium confidence

This code means that our number will be formatted as indicated above (“000-000-0000”), once the user enters a number.

We will also add a command button that will be able to close our User Form. We will go back to our User Form, go into the Toolbox, and reach out to the Command Button option. We will simply write Cancel on it and then click on it to apply the following code:

Private Sub CommandButton1_Click()
    Unload Me
End Sub

This code will simply allow us to close our User Form on click. Our User Form looks like this:

Graphical user interface

Description automatically generated

And our code related to the User Form is as follows:

Graphical user interface, text, application, email

Description automatically generated

Displaying and Closing User Form

Although we defined our User Form and the code related to it, users still cannot see this in our workbook. We need to attach some code to make this User Form visible.

We will go to our VBA window, right-click anywhere on the left window, and then choose Module:

Graphical user interface, text, application

Description automatically generated

Once in the Module window, we will input the following formula:

Sub DisplayUserForm()
UserForm1.Show
End Sub

And that is it. All we need to know for this formula to work is our User Form name, in our case UserForm1.

We will then save our code. Remember that you need to save your Workbook as a Macro-Enabled Workbook to have your codes stored.

We will get back to our worksheet then go to the Developer tab >> Controls >> Insert and select the first option available:

A screenshot of a computer

Description automatically generated with low confidence

Once we click on it, we will change the text inside to be: “Click to input a phone number”, then right-click on it and choose Assign Macro:

Graphical user interface, application

Description automatically generated

Once we are on this window, we will choose our Macro (in our case the only one available- DisplayUserForm:

Graphical user interface, text, application

Description automatically generated

Now our Macro will be assigned to the button and will generate our User Form, once clicked on.

All we need to do now is click on this button. We will be presented with the following window:

Graphical user interface, application

Description automatically generated

When we enter desired number, for example, 5555 (just for the fun of it), we will have the following picture on our screen:

Graphical user interface, application

Description automatically generated

When we click ENTER, our code for formatting will execute, and our number will be formatted:

Graphical user interface, application

Description automatically generated

You will notice that our number is formatted in the desired way.

It is worth noting, that, however, we do not have a code to write this number into our worksheet directly. We just show this number to our user, without remembering it into the memory.

All we can do now is click Cancel to exit the User Form.

Posted in vba