how to draw lines and more from drawing tools in excel using vb.net
hi all
i would like to know how to draw figures in excel programically with lines from drawing tools of excel using vb.net (as in attached pdf file).
So can any one guide me how to do it with an example?
thanks in advance
gvg
Last edited by gvgbabu; Oct 6th, 2017 at 04:14 AM.
Re: how to draw lines and more from drawing tools in excel using vb.net
thank you paul
I am writing an excel through vb.net program. I want to draw a diagram in excel with writing. i think it is not a VBA question.
any example how to do it with excel drawing tools through vb.net.
Re: how to draw lines and more from drawing tools in excel using vb.net
If you know how to manually do the kind of things you want to do, you can get Excel to tell you the code you need... simply record a macro and perform the actions.
The code won't be quite right for .Net , but it doesn't take too much work to modify it to use your object variables for the worksheet etc, and to use the .Net versions of the constants.
If you get stuck, show us the macro and your .Net version, and let us know the issue(s).
Re: how to draw lines and more from drawing tools in excel using vb.net
as per your advice i started to record macro
when i place lines and arrows from insert->shapes as per my example attached above it did not record any thing
except sub and end sub.
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Re: how to draw lines and more from drawing tools in excel using vb.net
as per your advice i started to record macro
when i place lines and arrows from insert->shapes as per my example attached above. It did not record any thing
except sub and end sub.
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Re: how to draw lines and more from drawing tools in excel using vb.net
Here's an example drawing two lines, the second of which has arrowheads. Both lines are drawn from the upper left of one cell to the upper left of another. These starting and ending points can be offset as necessary.
Code:
Sub addLines()
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim x1 As Integer
Dim y1 As Integer
Dim x2 As Integer
Dim y2 As Integer
Dim shp As Shape
Set ws = ActiveSheet
With ws
Set rng1 = .Range("b8")
Set rng2 = .Range("d2")
x1 = rng1.Left
y1 = rng1.Top
x2 = rng2.Left
y2 = rng2.Top
.Shapes.AddConnector msoConnectorStraight, x1, y1, x2, y2
Set rng1 = rng1.Offset(4, 2)
Set rng2 = rng2.Offset(4, 2)
x1 = rng1.Left
y1 = rng1.Top
x2 = rng2.Left
y2 = rng2.Top
Set shp = .Shapes.AddConnector(msoConnectorStraight, x1, y1, x2, y2)
With shp
.Line.BeginArrowheadStyle = msoArrowheadStealth
.Line.EndArrowheadStyle = msoArrowheadStealth
End With
End With
End Sub
You may need to go back to the .Net forum for help converting the code from Excel.
Re: how to draw lines and more from drawing tools in excel using vb.net
thank you vbfbryce for your reply
your code inserting the line or arrow at the starting point of the excel cell.
But i would like to ask you that is there any way to insert the line starting point except the starting point of the excel cell (ie at the mid point or 1/4 th point etc.)
Re: how to draw lines and more from drawing tools in excel using vb.net
ie at the mid point or 1/4 th point etc.
mid point of a cell or table or what?
for a cell try like x1 = rng1.left + rng1.width /4
same for height if required
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: how to draw lines and more from drawing tools in excel using vb.net
thanks,
it worked for me.
one more thing that i inserted right angle triangle, it inserted filled righttriangle. i would like to know how to use no fill property of that triangle.
Re: how to draw lines and more from drawing tools in excel using vb.net
set the visible property of the shapes fill to false
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: how to draw lines and more from drawing tools in excel using vb.net
my requirement is lefttriangle but there is only Righttriangle and there is no options for left triangle
so, is it possible to insert triangle slope from right to left using vb.net
Re: how to draw lines and more from drawing tools in excel using vb.net
Are you asking if you can draw a diagonal line from "right to left?" If so, yes, that's what I demonstrated in #10. Change the from and the to points according to your needs.
Re: how to draw lines and more from drawing tools in excel using vb.net
thanks vbfbryce,
yes that can possible. i am inserting a triangle. so, instead of inserting three lines i am going for triangle. is there any option to insert lefttriangle or modify with any property of Righttriangle?
if the answer is NO then i can go for three lines.
Re: how to draw lines and more from drawing tools in excel using vb.net
i believe a right triangle just means right angle, not whether is left or right position, testing appears to confirm this
just flip the shape, msohorizontal or vertical
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete