Hi
This function will return some of the cell's properties. It can be entered as an array formula on a worksheet or called by your other procedures in VBA. You can have it return the property name and Constant assigned to each of the properties or just return the values of the constants.
<pre>
Sub test()
Dim GetProps
GetProps = CellProps(Range("A1"), False, 0)
End Sub
Function CellProps(ChkCell As Range, InSheet As Boolean, Optional ReturnType As Byte = 0) As Variant
'InSheet. True if this Function will be used in a worksheet. False if in VBA
'ReturnType. 1 if returning the Property name and assigned constant. 0 to return the Contants value only
'Enter in worksheet as an array in seven cells in one column
Dim cp(6)
Application.Volatile
With ChkCell
cp(0) = .HorizontalAlignment
cp(1) = .VerticalAlignment
cp(2) = .WrapText
cp(3) = .Orientation
cp(4) = .AddIndent
cp(5) = .ShrinkToFit
cp(6) = .MergeCells
End With
If ReturnType = 1 Then
Select Case cp(0)
Case xlHAlignCenter: cp(0) = "HorizontalAlignment = xlHAlignCenter"
Case xlHAlignDistributed: cp(0) = "HorizontalAlignment = xlHAlignDistributed"
Case xlHAlignJustify: cp(0) = "HorizontalAlignment = xlHAlignJustify"
Case xlHAlignLeft: cp(0) = "HorizontalAlignment = xlHAlignLeft"
Case xlHAlignRight: cp(0) = "HorizontalAlignment = xlHAlignRight"
Case xlHAlignCenterAcrossSelection: cp(0) = "HorizontalAlignment = xlHAlignCenterAcrossSelection"
Case xlHAlignFill: cp(0) = "HorizontalAlignment = xlHAlignFill"
Case xlHAlignGeneral: cp(0) = "HorizontalAlignment = xlHAlignGeneral"
End Select
Select Case cp(1)
Case xlVAlignBottom: cp(1) = "VerticalAlignment = xlVAlignBottom"
Case xlVAlignCenter: cp(1) = "VerticalAlignment = xlVAlignCenter"
Case xlVAlignDistributed: cp(1) = "VerticalAlignment = xlVAlignDistributed"
Case xlVAlignJustify: cp(1) = "VerticalAlignment = xlVAlignJustify"
Case xlVAlignTop: cp(1) = "VerticalAlignment = xlVAlignTop"
End Select
cp(2) = "WrapText = " & cp(2)
Select Case cp(3)
Case xlDownward: cp(3) = "Orientation = xlDownward"
Case xlHorizontal: cp(3) = "Orientation = lHorizontal"
Case xlUpward: cp(3) = "Orientation = xlUpward"
Case xlVertical: cp(3) = "Orientation = xlVertical"
Case Else: cp(3) = "Orientation = " & cp(3) & " degrees"
End Select
cp(4) = "AddIndent = " & cp(4)
cp(5) = "ShrinkToFit = " & cp(5)
cp(6) = "MergeCells = " & cp(6)
If InSheet Then
CellProps = Application.WorksheetFunction.Transpose(cp)
Else: CellProps = cp
End If
Exit Function
End If
If InSheet Then
CellProps = Application.WorksheetFunction.Transpose(cp)
Else: CellProps = cp
End If
End Function
</pre>
Tom