How Can I Check Cell Justification?

[murphy00]

I know there has to be a way to get the cell justification in VBA. Does anyone know how?

[kkknie]

Use the .HorizontalAlignment property. It will return an integer corresponding to the type of alignment.

For example:

Left: -4131
Right: -4152
Center: -4108
General: 1

There are others, but I didn't check them all. They can be used in code using their Excel constants (i.e. xlLeft, xlRight, xlCenter, xlGeneral). All constants can be found using the Object browser for the Excel library in VBA.

To set the alignment of a range, simply use Range("A1").HorizontalAlignment = xlCenter

K

[ This Message was edited by: kkknie on 2002-05-20 07:18 ]

[Lo Lun To]

Here's another way :-

Dim x%, align$
x = Application.ExecuteExcel4Macro("GET.cell(8)")
Select Case x
Case 1: align = "General"
Case 2: align = "Left"
Case 3: align = "Center"
Case 4: align = "Right"
Case 5: align = "Fill"
Case 6: align = "Justified"
Case 7: align = "Centered across cells"
End Select
MsgBox "The active cell aligment is " & align

[Joe Was]

This is a MsgBox Utility I use when designing Sheets. With one hot-key it gives most of the cell selection data you need to design or debug a cell. It will tell you the cell: format, alignment, type, size, ect. I have found that formulas or code may not work or display right if your cell is of a format different then your formula or code wants, this utility will tell you "what you have" with one hot-key. When formating rows or columns in different parts of your sheet it saves you from going to several pull-downs for information. You can also see how I coded the info to help you code your own. Hope it helps. JSW

Sub myCDType()
'Cell type(Data, Format & Formula test), of single cell in MsgBox.
'Load code in "Module."
'Best if run by Hot-key(Macros-Macro-Options).
'
'Test for formula in cell.
Dim myF As String
If Selection.HasFormula = True Then myF = " and contains a [Formula]." Else _
myF = " and contains [No Formula]."
'Test Alignment of selection?
If Selection.HorizontalAlignment = xlGeneral Then X = "General" Else
If Selection.HorizontalAlignment = xlLeft Then X = "Left" Else
If Selection.HorizontalAlignment = xlCenter Then X = "Center" Else
If Selection.HorizontalAlignment = xlRight Then X = "Right" Else
If Selection.HorizontalAlignment = xlFill Then X = "Fill" Else
If Selection.HorizontalAlignment = xlJustify Then X = "Justify" Else
If Selection.HorizontalAlignment = xlCenterAcrossSelection Then X = "Center Across Selection" Else
If Selection.HorizontalAlignment = xlDistributed Then X = "Distributed" Else
If Selection.VerticalAlignment = xlTop Then Y = "Top" Else
If Selection.VerticalAlignment = xlCenter Then Y = "Center" Else
If Selection.VerticalAlignment = xlBottom Then Y = "Bottom" Else
If Selection.VerticalAlignment = xlJustify Then Y = "Justify" Else
If Selection.VerticalAlignment = xlDistributed Then Y = "Distributed" Else
Select Case True
'Test cell data type & format, return to MsgBox for display.
'Note: For some reason the case-order is important!
'
'Cell is Blank, Cell Format & Formula?
Case IsEmpty(Selection): MsgBox "The Cell Data is: [Blank] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Text, Cell Format & Formula?
Case Application.IsText(Selection): MsgBox "The Cell Data is: [Text] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Date, Cell Format & Formula?
Case IsDate(Selection): MsgBox "The Cell Data is a: [Date] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Time, Cell Format & Formula?
Case InStr(1, Selection.Text, ":") <> 0: MsgBox "The Cell Data is a: [Time] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Logical, Cell Format & Formula?
Case Application.IsLogical(Selection): MsgBox "The Cell Data is: [Logical] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Error, Cell Format & Formula?
Case Application.IsErr(Selection): MsgBox "The Cell Data is an: [Error] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Value, Cell Format & Formula?
Case IsNumeric(Selection): MsgBox "The Cell Data is a: [Value] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
End Select
End Sub

[Joe Was]

This part of the code will return a MsgBox with the alignment. JSW

Sub Align()
'Test Alignment of selection?
If Selection.HorizontalAlignment = xlGeneral Then X = "General" Else
If Selection.HorizontalAlignment = xlLeft Then X = "Left" Else
If Selection.HorizontalAlignment = xlCenter Then X = "Center" Else
If Selection.HorizontalAlignment = xlRight Then X = "Right" Else
If Selection.HorizontalAlignment = xlFill Then X = "Fill" Else
If Selection.HorizontalAlignment = xlJustify Then X = "Justify" Else
If Selection.HorizontalAlignment = xlCenterAcrossSelection Then X = "Center Across Selection" Else
If Selection.HorizontalAlignment = xlDistributed Then X = "Distributed" Else
If Selection.VerticalAlignment = xlTop Then Y = "Top" Else
If Selection.VerticalAlignment = xlCenter Then Y = "Center" Else
If Selection.VerticalAlignment = xlBottom Then Y = "Bottom" Else
If Selection.VerticalAlignment = xlJustify Then Y = "Justify" Else
If Selection.VerticalAlignment = xlDistributed Then Y = "Distributed" Else
MsgBox "Horizontal: [" & X & "] and Vertical: [" & Y & "]."
End Sub