Text Alignment with VBA Command Button in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn text alignment with the command button in Excel using VBA. Text alignment is a necessary thing to represent a clean dataset. We can align texts manually from the Home tab. But today, we will use VBA to perform text alignment with the command button in the Excel worksheet.


5 Methods of Text Alignment with VBA Command Button in Excel

To explain these methods, we will use a dataset that contains information about departments and the age of some employees. We will try to implement different types of alignment here.

Excel VBA Command Button to Implement Right Text Alignment


1. Excel VBA Command Button to Implement Right Text Alignment

In this method, we will add a command button and use it to align the texts on the right side of the cell and will use horizontal alignment. Our dataset has middle and center alignment right now.

Excel VBA Command Button to Implement Right Text Alignment

Let’s follow the steps below to learn more.

STEPS:

  • In the first place, go to the Developer tab.
  • Select Insert. A drop-down menu will occur.
  • Select the CommandButton icon. This action will let you draw the command button in the worksheet.

Excel VBA Command Button to Implement Right Text Alignment

  • Secondly, draw a rectangular shape in your worksheet. You will see the below result after drawing.

Excel VBA Command Button to Implement Right Text Alignment

  • Thirdly, double-click on the command button and it will open a Module window like below.

Excel VBA Command Button to Implement Right Text Alignment

  • Now, type the code in the Module.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Right_Alignment"
Selection.HorizontalAlignment = xlRight
End Sub

Excel VBA Command Button to Implement Right Text Alignment

Here, this code will first set the name of the command button to Right_Alignment and then it will perform horizontal alignment on the right side.

  • Press Ctrl + S to save the code and then press the F5 key.
  • After that, you will see results like below.

Excel VBA Command Button to Implement Right Text Alignment

  • Next, select the cells where you want to apply the right alignment.

Excel VBA Command Button to Implement Right Text Alignment

  • Finally, click on the command button to see results like below.

Excel VBA Command Button to Implement Right Text Alignment


2. Create Left Text Alignment with Excel VBA Command Button

We can also align the texts on the left side of the cells. We will use the same dataset here again.

Create Left Text Alignment with Excel VBA Command Button

Let’s pay attention to the steps below.

STEPS:

  • Firstly, go to the Developer tab and select Insert. Then, select the CommandButton icon from the drop-down menu.

Create Left Text Alignment with Excel VBA Command Button

  • Secondly, draw the command button in the worksheet.

Create Left Text Alignment with Excel VBA Command Button

  • After that, double-click on the command button and type the code in the Module window:
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Left_Alignment"
Selection.HorizontalAlignment = xlLeft
End Sub

Create Left Text Alignment with Excel VBA Command Button

This code will change the caption of the command button using CommandButton1.Caption and then align the selected cells on the left side horizontally.

  • Now, press Ctrl + S to save the code.
  • Then, hit the F5 key to run the code.
  • After running the code, you will see results like below.

Create Left Text Alignment with Excel VBA Command Button

  • Next, select the cells where you want to implement the left alignment.

Create Left Text Alignment with Excel VBA Command Button

  • In the end, click on the command button to see the texts on the left side of the cells.

Create Left Text Alignment with Excel VBA Command Button

Read More: VBA Code for Save Button in Excel 


3. Apply Excel VBA to Perform Center Text Alignment with Command Button

In this method, we will try to apply center text alignment. This time our dataset is left aligned.

Apply Excel VBA to Perform Center Text Alignment with Command Button

Let’s follow the steps below.

STEPS:

  • Go to the Developer tab and select Insert at first.
  • Select the CommandButton icon from the drop-down menu.

Apply Excel VBA to Perform Center Text Alignment with Command Button

  • After that, draw the command button on the worksheet.

Apply Excel VBA to Perform Center Text Alignment with Command Button

  • Now, double-click the command button and type the code in the Module window.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Center_Alignment"
Selection.HorizontalAlignment = xlCenter
End Sub

Apply Excel VBA to Perform Center Text Alignment with Command Button

This code is using xlCenter instead of xlLeft from the previous code.

  • Next, save the code by pressing Ctrl + S and then, run it by pressing the F5 key.
  • After running the code, you will see results like below in the worksheet.

Apply Excel VBA to Perform Center Text Alignment with Command Button

  • To implement the center alignment, select the cells.

Apply Excel VBA to Perform Center Text Alignment with Command Button

  • Finally, click on the command button to see results like below.

Apply Excel VBA to Perform Center Text Alignment with Command Button


4. Vertical Text Alignment with Excel Command Button Using VBA

In the previous methods, we used horizontal alignment. We will use vertical alignment here. To explain this method easily, we will use the same dataset but with fewer rows.

Vertical Text Alignment with Excel Command Button Using VBA

Let’s observe the steps below.

STEPS:

  • In the beginning, go to the Developer tab and select Insert. Then, select the CommandButton icon from the drop-down menu.

Vertical Text Alignment with Excel Command Button Using VBA

  • After that, draw the command button on the worksheet.

Vertical Text Alignment with Excel Command Button Using VBA

  • Now, double-click the command button to open the Module window.
  • Then, type the code there.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Vertical_Alignment"
Selection.VerticalAlignment = xlCenter
End Sub

Vertical Text Alignment with Excel Command Button Using VBA

This VBA code will align the texts in the center vertically.

  • Next, press Ctrl + S to save the code.
  • After saving, press the F5 key to run the code. The name of the command button will change.

Vertical Text Alignment with Excel Command Button Using VBA

  • At this moment, select the cells to perform center alignment vertically.

Vertical Text Alignment with Excel Command Button Using VBA

  • In the end, hit the command button to see results like below.

  • To align the texts at the top of the cell, use the code below.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Verical_Alignment"
Selection.HorizontalAlignment = xlTop
End Sub

  • Running the above code, you will see results like below.

  • Use the code below to align at the bottom vertically.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Center_Alignment"
Selection.HorizontalAlignment = xlBottom
End Sub

  • Lastly, you will see the bottom alignment vertically like below.

Read More: How to Use VBA Code for Submit Button in Excel 


5. Horizontal & Vertical Alignment Together with VBA Command Button

We can also implement horizontal and vertical alignment together. Here, we will use the previous dataset.

Let’s follow the steps below.

STEPS:

  • Firstly, go to the Developer tab and select Insert. Then, select the CommandButton icon from the drop-down menu.

  • Secondly, draw the command button in the worksheet.

  • Thirdly, double-click the command button, and a Module window will open.
  • Type the code below in the Module window.
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Horizontal_Vertical_Alignment"
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
End Sub

Here, this code is performing center alignment both horizontally and vertically.

  • Now, press Ctrl + S to save the code and then, the F5 key to run the code.

  • After that, select the cells where you want to apply the code.

  • Finally, click on the command button to see results like below.


Things To Remember

In the above codes, we have written the caption of the command button using CommandButton.Caption. You can delete this part in the code if you want to just align texts without naming the command button.


Download Practice Book

Download the practice book here.


Conclusion

We have demonstrated 5 easy and quick VBA examples to align texts with the command button in Excel. I hope these methods will help you to align texts with the command button using VBA. Furthermore, the practice book is added at the beginning of the article. You can download the practice book to exercise more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo