Format Column as Text with VBA

You probably already know that VBA (Visual Basic for Applications) is an amazing tool to use to automate processes in Excel. It is also a great tool to help potential users do various things.

In the example below, we will present how to format any given column as text with the help of VBA.

Format Column as Text with VBA

For our example, we will input random numbers from 1 to 100 in range A2:H11:

Table

Description automatically generated

To format any of these columns as Text manually, we need to select any column (for example column C), then go to the Home tab and in Numbers sub-section choose Text on a dropdown:

Graphical user interface, application, table, Excel

Description automatically generated

Now our table with random numbers looks like this:

Table

Description automatically generated

Let us now try to do these steps, but by using VBA. First thing first, we need to access the module by clicking the combination of ALT + F11.

Once in the module, we will right-click on it and choose Insert >> Module:

Graphical user interface, application

Description automatically generated

When we click on it, we will input the following code on the right side of our screen:

Sub FormatAsText()
Dim col As String
col = InputBox(PROMPT:="Which column do you want to convert to text?")
Columns(col).Select
Selection.NumberFormat = "@"
End Sub

This is what our code looks like in the module:

Graphical user interface, text, application, email

Description automatically generated

What our code does is it first creates a “col” variable and then declare that variable to be equal to the answer or the user on the question below:

Dim col As String
col = InputBox(PROMPT:="Which column do you want to convert to text?")

Then we select the column that the user wanted to change and change the format of that selection (that column) to be text.

Columns(col).Select
Selection.NumberFormat = "@"

Now, our code has a significant error, and that is it will simply fail if the user clicks something other than an actual column name, but for the sake of this exercise, we will presume that our user knows what we are talking about.

We will now add our code to our sheet by going to it, and then going to Developer tab >> Controls >> Insert >> Form Controls >> Button (first option available):

Graphical user interface, application, table, Excel

Description automatically generated

We will then insert the button next to our table. As soon as we set the destination, we will be presented with the following window:

Graphical user interface, text, application

Description automatically generated

We will assign our existing macro (FormatAsText) to our button, which means that we will be able to run our macro by clicking on our button.

We will change the name of our button to be “Format as Text”.

Table

Description automatically generated with medium confidence

Now, when we click on this button, our code will be executed. The first thing that the user will see is the pop-up window with the question we defined:

Graphical user interface, text, application, email

Description automatically generated

We will choose column G, for the sake of the exercise.

Graphical user interface, text, application, email

Description automatically generated

When we click OK, we will see that our column G has been formatted:

Graphical user interface, table

Description automatically generated

Posted in vba