cell Properties

[suresh_bowman]

how can i find the alignment of the text in a particualr cell from Visual Basic
This message was edited by suresh_bowman on 2002-08-29 05:01

 

[Legacy 98055]

Hi
I could not figure out a way to return the Constant name but was able to get the values.
<pre>
Sub test()
Dim GetProps
GetProps = CellProps(Range("A1"))
Debug.Print GetProps(1)
Debug.Print GetProps(2)
Debug.Print GetProps(3)
Debug.Print GetProps(4)
Debug.Print GetProps(5)
Debug.Print GetProps(6)
Debug.Print GetProps(7)

End Sub

Function CellProps(ChkCell As Range) As Variant
Dim CellProperties(7) As String
With ChkCell
CellProperties(1) = .HorizontalAlignment
CellProperties(2) = .VerticalAlignment
CellProperties(3) = .WrapText
CellProperties(4) = .Orientation
CellProperties(5) = .AddIndent
CellProperties(6) = .ShrinkToFit
CellProperties(7) = .MergeCells
End With
CellProps = CellProperties
End Function

</pre>
Tom

 

[smozgur]

Welcome to the board suresh,

'Horizontal Alignment value for Cell A1
Range("A1").HorizontalAlignment
'Vertical Alignment value for Cell A1
Range("A1").VerticalAlignment

return values

2 : Left Aligned
3 : Center Aligned
4 : Right Aligned
(5 : Justify, 1 : Distributed)

You can also set this read/write properties via VBA.

Regards
Suat

 

[Legacy 98055]

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