Excel VBA, Get Cell Alignment Properties (Sample Code + Download)
In this article I’ve provided a sample code that will get the alignment properties of the cells in the first row. The way it works is that whenever the user presses the run button the alignment properties of the cells B1 to D1 will be displayed in the rows below. For the default alignment settings:
By randomly changing the horizontal and vertical alignment of the cells in row 1 and pressing run:
By randomly changing the orientation and pressing run:
In the figure below all the alignment properties have been randomly assigned values:
The code below is used to get the alignment properties:
'executes when the button run is clicked
Private Sub btnRun_Click()
Dim i As Integer
'loops through the cells in row 1
For i = 1 To 3
'gets the horizontal alignment
Call printHRAl(i + 1)
'gets the vertical alignment
Call printVRAl(i + 1)
'get the orientation
Call printORAl(i + 1)
'gets the text wrap property
Call printWrapText(i + 1)
'gets the shrink to fit property
Call printShrink2Fit(i + 1)
'gets the reading order
Call printReadingOrder(i + 1)
Next i
End Sub
The function printHRAl() gets the horizontal alignment of the cells and prints the value retrieved. For more information about working with the horizontal alignment property in VBA please see Excel VBA Horizontal Alignment:
'gets the horizontal alignment
Private Sub printHRAl(ByVal intColumn As Integer)
'checks if the text is left aligned
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlLeft Then
Cells(2, intColumn) = "Left"
'checks if the text is center aligned
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlCenter Then
Cells(2, intColumn) = "Center"
'checks if the text is right aligned
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlRight Then
Cells(2, intColumn) = "Right"
'checks if the fill alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlFill Then
Cells(2, intColumn) = "Fill"
'checks if the distributed alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlDistributed Then
Cells(2, intColumn) = "Distributed"
'checks if the center across selection alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlCenterAcrossSelection Then
Cells(2, intColumn) = "Center Across Selection"
'checks if the justify alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlJustify Then
Cells(2, intColumn) = "Justify"
'checks if the general alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).HorizontalAlignment = xlGeneral Then
Cells(2, intColumn) = "General"
End If
End Sub
The function printVRAl() gets the vertical alignment of the cells and prints the value retrieved. For more information about working with the vertical alignment property in VBA please see Excel VBA Vertical Alignment:
'gets the vertical alignment
Private Sub printVRAl(ByVal intColumn As Integer)
'checks if the top alignment is used
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlTop Then
Cells(3, intColumn) = "Top"
'checks if the center alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlCenter Then
Cells(3, intColumn) = "Center"
'checks if the button alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlBottom Then
Cells(3, intColumn) = "Bottom"
'checks if the justify alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlJustify Then
Cells(3, intColumn) = "Justify"
'checks if the distributed alignment is used
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).VerticalAlignment = xlDistributed Then
Cells(3, intColumn) = "Distributed"
End If
End Sub
The function printORAl() gets the text orientation and prints the value retrieved. For more information working with the text orientation property in VBA please see Excel VBA Orientation ( Alignment):
'gets the cells orientation
Private Sub printORAl(ByVal intColumn As Integer)
'if the cell does not have an orientation the number -4128 is returned
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).Orientation <> -4128 Then
Cells(4, intColumn) = Range(Cells(1, _
intColumn), Cells(1, intColumn)).Orientation
Else
Cells(4, intColumn) = 0
End If
End Sub
The function printWrapText() gets the wrap text property and prints the value retrieved. For more information working with the wrap text property in VBA please see Excel VBA Wrap Text On/Off:
'checks if the wrap text property is on or off
Private Sub printWrapText(ByVal intColumn As Integer)
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).WrapText = True Then
Cells(5, intColumn) = True
Else
Cells(5, intColumn) = False
End If
End Sub
The function printShrink2Fit() gets the shrink to fit property and prints the value retrieved. For more information working with the shrink to fit property in VBA please see Excel VBA Shrink to Fit On/Off:
'checks if the shrink text property is on or off
Private Sub printShrink2Fit(ByVal intColumn As Integer)
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).ShrinkToFit = True Then
Cells(6, intColumn) = True
Else
Cells(6, intColumn) = False
End If
End Sub
The function printReadingOrder() gets the reading order property and prints the value retrieved. For more information working with the reading order property in VBA please see Excel VBA Reaing Order:
'gets the reading order property
Private Sub printReadingOrder(ByVal intColumn As Integer)
If Range(Cells(1, intColumn), Cells(1, _
intColumn)).ReadingOrder = xlContext Then
Cells(7, intColumn) = "Context"
ElseIf Range(Cells(1, intColumn), Cells(1, _
intColumn)).ReadingOrder = xlLTR Then
Cells(7, intColumn) = "Left to Right"
Else
Cells(7, intColumn) = "Right to Left"
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