Creating an organization diagram in Excel

[sedky]

Hi,

I am trying to create an organization chart programmatically in excel.
I got this code from a previous posting, but adding text doesn't work.
I also need to link the boxes together. Can someone please post some
code on how to do that?

Thanks

Dim shDiagram as Shape
Dim nodRoot As DiagramNode
Set shDiagram = ActiveSheet.Shapes.AddDiagram( _
Type:=msoDiagramOrgChart, Top:=10, _
Left:=15, Width:=400, Height:=475)
Set nodRoot = shDiagram.DiagramNode.Children.AddNode
nodRoot.TextShape.TextFrame.Characters.Text = "First Node"

 

[NickHK]

sedky,
Record a macro creating 2 shapes and placing a connctor between them, from
the options on the Drawing toolbar. You'll get code that looks like:

ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess, 97.5, 171#, 127.5,
48.75).Select
ActiveSheet.Shapes.AddShape(msoShapeFlowchartPreparation, 327.75, 95.25,
197.25, 54#).Select
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 225#, 148.5, 110.25,
47.25).Select
Selection.ShapeRange.ConnectorFormat.BeginConnect
ActiveSheet.Shapes("AutoShape 1"), 4
Selection.ShapeRange.ConnectorFormat.EndConnect
ActiveSheet.Shapes("AutoShape 2"), 2

NickHK

 

[sedky]

For some reason, I don't have the macro recorder. The macro security is
set to Low !!
By the way the organizational chart is better than the flowchart.

 

[NickHK]

sedky,
If you cannot record a macro then how are you going to run any VBA code.
I assume this is a feature added after Excel 9, but I would imagine
something similar would apply.

NickHK

 

[NickHK]

"this" refers to the organisational chart, not inability of running macros,
in case anyone was in doubt.

NickHK

 

[sedky]

I am able to run VBA code. I am running Excel 2003 by the way, but for
some reason "Record Macro" is not on the list. The code that I
originally pasted is fully functional (even the code you suggested),
but I wanted to get an idea of how to link up those organizational
chart boxes.