Results 1 to 20 of 20

Thread: how to draw lines and more from drawing tools in excel using vb.net

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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
    Attached Images Attached Images
    Last edited by gvgbabu; Oct 6th, 2017 at 04:14 AM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: how to draw lines and more from drawing tools in excel using vb.net

    I don't understand the question...
    If you want to draw in excel, it's a VBA question, and you should ask in the Office Development Forum

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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).

  5. #5

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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
    '

    End Sub

    please help me

  6. #6

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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
    '

    End Sub

    please help me

  7. #7
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: how to draw lines and more from drawing tools in excel using vb.net

    You're right. Recording a macro and inserting shapes doesn't generate any code. I don't know the solution to your problem...

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: how to draw lines and more from drawing tools in excel using vb.net

    You really will get better replies in the Office Development forum

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: how to draw lines and more from drawing tools in excel using vb.net

    You are probably right, so I've moved this thread the 'Office Development' forum.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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.

  11. #11

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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.)

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  13. #13

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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.

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  15. #15

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Re: how to draw lines and more from drawing tools in excel using vb.net

    thanks westconn1

    its worked.

  16. #16

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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

  17. #17
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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.

  18. #18

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    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.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  20. #20

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    58

    Resolved Re: how to draw lines and more from drawing tools in excel using vb.net

    thanks westconn1

    it's worked.
    Last edited by gvgbabu; Aug 21st, 2018 at 12:27 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width