Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    creating text boxes (2000 SP3)

    I have an interesting project at hand.

    I have a system which is represented by a hierarchial object structure in VBA classes.

    The client would like to see a diagramatic representation of this, preferrably in Excel.

    An idea I had for this is to create textboxes with the appropriate connecters on a spreadsheet, however I am running into some problems drawing these objects.

    Shapes.AddTextbox(msoTextOrientationHorizontal, lLeft, 0, 50, 20) allows me to add a text box, but I can't then set it's text or colour, etc.

    Also, I'm unsure as to go about adding connectors between these objects, e.g. arrows.

    Can anyone help?

    Many thanks,

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: creating text boxes (2000 SP3)

    OK, I found out how to set it's text at last. It's the Shape.TextFrame.Characters.Text property.

    Still no idea how to add the connectors though.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: creating text boxes (2000 SP3)

    Is this any help? (It's XP, can't currently check 2K)

    Dim shpConnector As Shape
    Set shpConnector = ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 147.75, 152.25, 136.5, 87#)
    With shpConnector.ConnectorFormat
    .BeginConnect ActiveSheet.Shapes("AutoShape 2"), 3
    .EndConnect ActiveSheet.Shapes("AutoShape 3"), 1
    End With

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: creating text boxes (2000 SP3)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Jun-04 14:38. Added "Rerouteconnections" to get shortest path)</P>This adds 2 textBoxes and connects them with an arrow.

    Steve

    <pre>Option Explicit
    Sub ConnectedTextBoxes()
    Dim shp1 As Shape
    Dim shp2 As Shape
    Dim shp3 As Shape
    With ActiveSheet.Shapes
    Set shp1 = .AddTextbox(msoTextOrientationHorizontal, 80, 30, 100, 50)
    Set shp2 = .AddTextbox(msoTextOrientationHorizontal, 80, 150, 100, 50)

    Set shp3 = .AddConnector(msoConnectorStraight, 0, 0, 0, 0)
    End With
    shp3.Line.EndArrowheadStyle = msoArrowheadTriangle
    With shp3.ConnectorFormat
    .BeginConnect shp1, 1
    .EndConnect shp2, 1
    End With
    shp3.RerouteConnections
    End Sub
    </pre>


  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: creating text boxes (2000 SP3)

    An alternative way is to do it with charting and Data Labels. See an example "Hierachy Chart" in <post#=341620>post 341620</post#>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I would imagine any software that did OrgCharts or flowcharting would be easier to use a setup since it would be designed to do this automatically.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: creating text boxes (2000 SP3)

    Thanks to both Brooke and sdckapr.

    I'm sure I'll be able to figure it out with the pointers you've given me.

Posting Permissions

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