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

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

Upon selecting a new vertical alignment the vertical alignment of the cell below the drop down list 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 the user selects a new value from the drop down lists
Private Sub worksheet_change(ByVal target As Range)
Dim i As Integer
'loops through all the columns
For i = 1 To 6
   Call ChangeFormat(Cells(1, i), i)
Next i
End Sub


The function ChangeFormat() receives to parameters as input:

1. strCommand: This is the formatting to be applied, “Top”, “Bottom”, “Center”, …
2. intColumn: The column to apply the changes to.

'Changes the vertical alignment of the cell in row 2 and the column specified
'by the input parameter intColumn
Private Sub ChangeFormat(ByVal strCommand As String, ByVal intColumn _
As Integer)
'apply top alignment
If strCommand = "Top" Then
    Range(Cells(2, intColumn), Cells(2, intColumn)).VerticalAlignment _
    = xlTop
'apply center alignment
ElseIf strCommand = "Center" Then
    Range(Cells(2, intColumn), Cells(2, intColumn)).VerticalAlignment _
    = xlCenter
'apply bottom alignment
ElseIf strCommand = "Bottom" Then
    Range(Cells(2, intColumn), Cells(2, intColumn)).VerticalAlignment _
    = xlBottom
'apply justify alignment
ElseIf strCommand = "Justify" Then
    Range(Cells(2, intColumn), Cells(2, intColumn)).VerticalAlignment _
    = xlJustify
'apply distributed alignment
ElseIf strCommand = "Distributed" Then
    Range(Cells(2, intColumn), Cells(2, intColumn)).VerticalAlignment = _
    xlDistributed
End If
End Sub


Result:

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

See also:

• Excel VBA, Vertical Alignment
• Excel VBA, Alignment
• Excel VBA, Get Vertical 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