CODEWITHSUNDEEP
excelvb.netvbacellshapes
Bob
Bob

Reputation: 1396

VB.Net: Inserting Shape in Certain Cell

I'm attempting to insert a shape at a specific cell, say (5,5) for instance. I am able to get the shape into excel, but can't figure out how to put it in (5,5). After research, I know that shapes sit on top of cells in worksheets. I have also learned that .Range could be helpful here.

I'm just uncertain of how to put these pieces of the puzzle together to make my shape go to (5,5).

xlWorkSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeIsoscelesTriangle, 17, 0, 15, 13)

Also, am a beginner to vb.net so if you could dumb everything down i'd really appreciate it!

EDIT: Tried this code.. but it put the number 7 in (5,5) instead of the shape.

Dim aNew As MsoAutoShapeType = MsoAutoShapeType.msoShapeIsoscelesTriangle xlWorkSheet.Cells(5, 5) = anew

Also tried:

xlWorkSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeIsoscelesTriangle, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Left, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Top, 15, 13)

but received an error of

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred

EDIT: Code that worked...

xlWorkSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, (xlWorkSheet.Cells(3, 5)).Left, (xlWorkSheet.Cells(3, 5)).Top, 25, 14)

Upvotes: 1

Views: 1152

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

something along these linesxlWorkSheet.get_range(xlWorkSheet.cells(5.5)).top or cells(5.5).top

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred

Debug, use xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Top breaking it down, so, is xlWorkSheet ok, is xlWorkSheet.Cells(5, 5) ok, is xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)) ok, where is the error, break down big statements, in fact start with them in their constituent parts, then look at their returns and chain together as you've done here xlWorkSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeIsoscel‌​esTriangle, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Left, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Top, 15, 13) We cant see your code/screen

Upvotes: 3

Preston
Preston

Reputation: 8177

Using VBA I'd go for this to print in B2, you can use height and width to change button height and width:

Dim button As Shape
Set button = ActiveSheet.Shapes("Button 1")
button.Top = Range("B2").Top
button.Left = Range("B2").left
button.Height = 50
button.Width = 100

or in your example:

xlWorkSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeIsoscelesTriangle, Range("B2").left, Range("B2").Top, 15, 13)

Upvotes: 2

Related Questions