VB.Net: Inserting Shape in Certain Cell

Question

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)

I thought they were similar in this regard; My apologies if they aren't! — Bob, Dec 12 '16 at 16:43
can you get the left and top properties from the cell? in vba `cells(5,5).top` — Nathan_Sav, Dec 12 '16 at 16:43
@Nathan_Sav that's what I was reading about, but i'm not sure how to go about it — Bob, Dec 12 '16 at 16:44
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range(v=vs.120).aspx — Nathan_Sav, Dec 12 '16 at 16:46
@Nathan_Sav I've looked into that, but i'm confused on the application of it — Bob, Dec 12 '16 at 16:48
Its not that difficult that assigning a worksheet object xlWorksheet as you've done. xlWorkSheet.get_range(xlWorkSheet.cells(5.5)).top maybe, not in VS at mo. — Nathan_Sav, Dec 12 '16 at 16:51
@Nathan_Sav That makes perfect sense on paper but for some reason I'm getting an error when I try to run it. See EDIT in original post — Bob, Dec 12 '16 at 17:18
Debug it then, 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.msoShapeIsoscelesTriangle, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Left, xlWorkSheet.Range(xlWorkSheet.Cells(5, 5)).Top, 15, 13)` We cant see your code/screen, — Nathan_Sav, Dec 12 '16 at 17:22
@Nathan_Sav if you make an answer i'll accept it. Finally got it to work with your help. — Bob, Dec 12 '16 at 17:40

score 3 · Accepted Answer · edited Dec 13 '16 at 09:52

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

score 2 · Answer 2 · answered Dec 12 '16 at 16:48

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)

VB.Net: Inserting Shape in Certain Cell

2 Answers2