Excel VBA, Set Horizontal Alignment, (Sample Code)

In this article I will provide an example on how to set the horizontal alignment of cells using VBA. The user selects the horizontal alignment from a set of drop down lists. The selected horizontal alignment will be applied to the cell adjacent to the drop down list.

You can download the file and code related to this article here.

VBA Excel, Formatting, Set Horizontal Alignment
Upon selecting a new horizontal alignment the horizontal alignment of the adjacent cell will change:

VBA Excel, Formatting, Set Horizontal Alignment, Center
The drop down lists are created using data validation. The list of items to populate the drop down list are in sheet2:

Excel VBA, Drop Down List Items, Sheet 2

For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists.

The program uses a worksheet_change() event handler which executes when the user selects new values from the drop down lists:

'triggers when changes are made to the worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 1 To 13
'loops through all the rows
Call ChangeFormat(Cells(i, 1), i)
Next i
End Sub

The function ChangeFormat() receives to parameters as input:

  1. strCommand: This is the formatting to be applied, “Left”, “Right”, “Center”, …
  2. intRow: The row to apply the changes to.

'Changes the horizontal alignment of the cell in column B and row specified
'by the input parameter intRow
Private Sub ChangeFormat(ByVal strCommand As String, ByVal intRow As Integer)
'apply center alignment
If strCommand = "Center" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlCenter
'apply left alignment
ElseIf strCommand = "Left" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlLeft
'apply right alignment
ElseIf strCommand = "Right" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlRight
'apply fill alignment
ElseIf strCommand = "Fill" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlFill
'apply justify alignment
ElseIf strCommand = "Justify" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlJustify
'apply center across selection alignment
ElseIf strCommand = "Center Across Selection" Then
Range(Cells(intRow, 2), Cells(intRow, 5)).Select
Selection.HorizontalAlignment = xlCenterAcrossSelection
'apply distributed alignment
ElseIf strCommand = "Distributed" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment _
= xlDistributed
'apply general alignment
ElseIf strCommand = "General" Then
Range(Cells(intRow, 2), Cells(intRow, 2)).HorizontalAlignment = xlGeneral
End If
End Sub

Result:

VBA Excel, Formatting, Set Horizontal Alignment Results
You can download the file and code related to this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *