creating text boxes (2000 SP3)

Topic

New Reply

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,

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

(Edited by sdckapr on 18-Jun-04 14:38. Added “Rerouteconnections” to get shortest path)

This adds 2 textBoxes and connects them with an arrow.

Steve

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

Reply | Quote

An alternative way is to do it with charting and Data Labels. See an example “Hierachy Chart” in post 341620

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

Reply | Quote

Thanks to both Brooke and sdckapr.

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

Reply | Quote

Thanks to both Brooke and sdckapr.

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

Reply | Quote