cell Properties

suresh_bowman

New Member
Joined
Aug 28, 2002
Messages
2
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
L

Legacy 98055

Guest
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

BatCoder
Joined
Feb 28, 2002
Messages
1,581
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
 
L

Legacy 98055

Guest
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
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top