Excel Cells Text Orientation VBA, C# (VSTO) code example

Excel Cells Text Orientation VBA, C# (VSTO) code example

Orientation

Property which sets text direction or text Orientation in a cell Excel sheet. There are four types of orientations can be set programmatically. XlOrientation enum provides following constants which can be used to set the orientation of text within cells or range:

  1. xlHorizontal
  2. xlDownward
  3. xlUpward
  4. xlVertical

In this article we will do a PoC where we will create one button and assign the code which will rotate the text within cell one by one direction using above constants.

VBA Code example

Public Sub OrientationTest()
    'Declare range object
    Dim oRange As Range
    'Bind selection to Range
    Set oRange = Range("OrientedRange")
    
    Dim counter As Integer
    If counter >= 0 Or counter < 5 Then
    counter = Range("Counter").Value + 1
        Select Case counter
            Case 1:
                oRange.Orientation = XlOrientation.xlHorizontal
                oRange.Interior.Color = vbRed
                Range("Counter").Value = counter
            Case 2:
                oRange.Orientation = XlOrientation.xlDownward
                Range("Counter").Value = counter
                oRange.Interior.Color = vbGreen
            Case 3:
                oRange.Orientation = XlOrientation.xlUpward
                Range("Counter").Value = counter
                oRange.Interior.Color = vbYellow
            Case 4:
                oRange.Orientation = XlOrientation.xlVertical
                Range("Counter").Value = 0
                oRange.Interior.Color = vbBlue
            Case Else
                oRange.Interior.Color = vbWhite
                DoEvents
        End Select
    End If
    
    'Memory cleanup
    Set oRange = Nothing
End Sub

C# code example

private void btnOrientation_Click(object sender, RibbonControlEventArgs e)
{
    //Declare range object
    excel.Range oRange = Globals.ThisAddIn.Application.ActiveSheet.Range("OrientedRange");
	//Declare counter range
	excel.Range oCounterRange= Globals.ThisAddIn.Application.ActiveSheet.Range("Counter");
	
	//counter
	int counter=0;
	
	if(counter >= 0 || counter <5)
	{
			counter = oCounterRange.Value + 1;
			
			if( counter == 1)
			{
				oRange.Orientation = excel.XlOrientation.xlHorizontal;
				oRange.Interior.Color=excel.XlRgbColor.rgbRed;
				oCounterRange.Value = counter;
			}
			else if( counter == 2)
			{
				oRange.Orientation = excel.XlOrientation.xlDownward;
				oRange.Interior.Color=excel.XlRgbColor.rgbGreen;
				oCounterRange.Value = counter;
			}
			else if( counter == 3)
			{
				oRange.Orientation = excel.XlOrientation.xlUpward;
				oRange.Interior.Color=excel.XlRgbColor.rgbGreen;
				oCounterRange.Value = counter;
			}
			else if( counter == 4)
			{
				oRange.Orientation = excel.XlOrientation.xlVertical;
				oRange.Interior.Color=excel.XlRgbColor.rgbBlue;
				oCounterRange.Value = 0;
			}
			else 
			{
				oRange.Interior.Color=excel.XlRgbColor.rgbWhite;
			}
	}
}

Explanation

Created two name ranges as “OrientedRange” which contains actual text which we will change and “Counter” which will hold a counter to rotate the each property to handle the Select Case statement. Finally the output would look like as below:

Output

Next >> Find, FindNext and FindPrevious in Excel Add-in

Leave a Reply

Your email address will not be published.