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.

Upon selecting a new horizontal alignment the horizontal alignment of the adjacent cell will change:

The drop down lists are created using data validation. The list of items to populate the drop down list are in sheet2:

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:

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

See also:

• Excel VBA, Horizontal Alignment
• Excel VBA, Alignment
• Excel VBA, Get Horizontal Alignment (Sample Code)

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