Excel VBA, Set Cell Alignment Properties (Sample Code + Download)
In this article I’ve provided a sample code that will set the alignment properties of the cells in the first row using VBA. The user selects different alignment properties using the drop down lists provided. The program then updates the alignment properties of the cells in row 1. In the figure below the cells in row 1 have the default alignment properties:
Using the drop down lists provided the horizontal alignment can be changed:
Using the drop down list in row 3 the vertical alignment can be changed:
By inputting values in row 4, the text orientation can be changed:
The drop down lists in row 5 can be used to change the wrap text property:
The drop down lists in row 6 can be used to change the shrink to fit property:
The drop down lists in row 7 can be used to change the reading order property:
The drop down lists are created using data validation. For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists. The values for the drop down lists are stored in sheet2:
The program has a Worksheet_Change event handler which executes when the user selects a value from the drop down lists:
'executes when the user makes changes to the worksheet
Private Sub Worksheet_Change(ByVal target As Range)
Dim i As Integer
For i = 1 To 3
'set horizontal alignment
Call setHRAL(i + 1)
'set vertical alignment
Call setVRAL(i + 1)
'set orientation
Call SetOR(i + 1)
'set text wrap
Call SetWrapText(i + 1)
'set shrink to fit
Call SetShrink2Fit(i + 1)
'set reading order
Call SetReadOrder(i + 1)
Next i
End Sub
The function setHRAl() sets the horizontal alignment of the cells. For more information about working with the horizontal alignment property in VBA please see Excel VBA Horizontal Alignment:
'sets the horizonatl alignment
Private Sub setHRAL(ByVal intColumn As Integer)
'General
If Cells(2, intColumn) = "General" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlGeneral
'Left
ElseIf Cells(2, intColumn) = "Left" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlLeft
'Center
ElseIf Cells(2, intColumn) = "Center" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlCenter
'Right
ElseIf Cells(2, intColumn) = "Right" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlRight
'Fill
ElseIf Cells(2, intColumn) = "Fill" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlFill
'Justify
ElseIf Cells(2, intColumn) = "Justify" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlJustify
'Distributed
ElseIf Cells(2, intColumn) = "Distributed" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlDistributed
End If
End Sub
The function setVRAL() sets the vertical alignment of the cells. For more information about working with the vertical alignment property in VBA please see Excel VBA Vertical Alignment:
'sets the vertical alignment
Private Sub setVRAL(ByVal intColumn As Integer)
'Top
If Cells(3, intColumn) = "Top" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlTop
'Center
ElseIf Cells(3, intColumn) = "Center" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlCenter
'Bottom
ElseIf Cells(3, intColumn) = "Bottom" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlBottom
'Justify
ElseIf Cells(3, intColumn) = "Justify" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlJustify
'Distributed
ElseIf Cells(3, intColumn) = "Distributed" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlDistributed
End If
End Sub
The function setOR() sets the text orientation. For more information working with the text orientation property in VBA please see Excel VBA Orientation ( Alignment):
'sets the orientation
Private Sub SetOR(ByVal intColumn As Integer)
Range(Cells(1, intColumn), Cells(1, _
intColumn)).Orientation = Cells(4, intColumn)
End Sub
The function setWrapText() sets the wrap text property. For more information working with the wrap text property in VBA please see Excel VBA Wrap Text On/Off:
'sets the wrap text property
Private Sub SetWrapText(ByVal intColumn As Integer)
If Cells(5, intColumn) = True Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).WrapText = True
Else
Range(Cells(1, intColumn), Cells(1, _
intColumn)).WrapText = False
End If
End Sub
The function setShrink2Fit() sets the shrink to fit property. For more information working with the shrink to fit property in VBA please see Excel VBA Shrink to Fit On/Off:
'sets the shrink to fit property
Private Sub SetShrink2Fit(ByVal intColumn As Integer)
If Cells(6, intColumn) = True Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).ShrinkToFit = True
Else
Range(Cells(1, intColumn), Cells(1, _
intColumn)).ShrinkToFit = False
End If
End Sub
The function setReadingOrder() sets the reading order property. For more information working with the reading order property in VBA please see Excel VBA Reaing Order:
'sets the read order property
Private Sub SetReadOrder(ByVal intColumn As Integer)
If Cells(7, intColumn) = "Left to Right" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).ReadingOrder = xlLTR
ElseIf Cells(7, intColumn) = "Right to Left" Then
Range(Cells(1, intColumn), Cells(1, _
intColumn)).ReadingOrder = xlRTL
Else
Range(Cells(1, intColumn), Cells(1, _
intColumn)).ReadingOrder = xlContext
End If
End Sub
You can download the code and file 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