CODEWITHSUNDEEP
c#exceloffice-interop
opelhatza
opelhatza

Reputation: 242

How to add a textbox at a specific position in an ExcelSheet?

Hello i try to add a Textbox on a specific place in an Excelsheet. Like from A34 to J39. But i have no clue how to do that.

Here is what i tried:

excelSheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 100, 100, 200, 50);

Works but the textbox appears on a random place on the sheet.

Microsoft.Office.Tools.Excel.Controls.TextBox
textBox1 = this.Controls.AddTextBox(
this.Range["A1", "B2"], "textBox1");
textBox1.Text = "Sample text";

This is from the MSDN Site. But wont work becouse you need a new mehtod for this and i am not supposed to add a new Method......

Range rng = UsedArea.Cells[rownum, cellnum];

txtbox = sheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, rng.Left, rng.Top, txt.Width / 2, rng.Height);

This is from SO but it says the range property cannot be changed...... Mabey it has something to do with the office interop 15 stuff.

So any help or advise would be great and thx for your time.

EDIT: Working C# Code to get a textbox in an excelsheet at any position:

    Excel.Range rng = excelSheet.get_Range("A34:J39");
    float left = (float)rng.Left;
    float top = (float)rng.Top;
    float width = (float)rng.Width;
    float height = (float)rng.Height;

    excelSheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, left, top, width, height).Select();

Upvotes: 3

Views: 792

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Using VBA:

Sub CoverRange()
    Dim r As Range
    Dim L As Long, T As Long, W As Long, H As Long
    Set r = Range("A34:J39")
    L = r.Left
    T = r.Top
    W = r.Width
    H = r.Height
    With ActiveSheet.Shapes
        .AddTextbox(msoTextOrientationHorizontal, L, T, W, H).Select
    End With
End Sub

You can also re-size and re-position the Shape once it has been created. Can you adapt this to your code ??

Upvotes: 1

Related Questions