Organization chart with VBA

Worf

Well-known Member

Joined

Oct 30, 2011

Messages

4,252

• Jun 1, 2020

• #1

Worf submitted a new Excel article:

Organization chart with VBA - This post shows how to create an organogram with VBA.

• Smart art can create organization charts, but it will have formatting limitations; the code below overcomes that.
• Prepare a source table containing the following information:
  • Columns A and B – the relationships between two elements. The shape fill color will come from column A.
  • Column C – descriptive text to be displayed.
  • Column D – auxiliary data to be placed next to the shape.
  • Column E – whether the shape has an outline or not.

• Now...

Click to expand...

Read more about this Excel article...

 

F

flipsidelive

New Member

Joined

May 10, 2021

Messages

2

Office Version

1. 2019

Platform

1. Windows

• May 11, 2021

• #2

This looks brilliant but I'm struggling to get it working. I get to this line, 'CommandBars.ExecuteMso ("SmartArtConvertToShapes")' and I get 'Object Variable not set. I'm on the latest version of office. Can anyone help?

 

Worf

Well-known Member

Joined

Oct 30, 2011

Messages

4,252

• May 13, 2021

• #3

I will analyze it as soon as possible...

 

Worf

Well-known Member

Joined

Oct 30, 2011

Messages

4,252

• May 14, 2021

• #4

What do you get when running this?

VBA Code:

Sub test()
MsgBox Val(Application.Version)
End Sub

 

F

flipsidelive

New Member

Joined

May 10, 2021

Messages

2

Office Version

1. 2019

Platform

1. Windows

• May 18, 2021

• #5

Hi. It says 16

 

Worf

Well-known Member

Joined

Oct 30, 2011

Messages

4,252

• May 20, 2021

• #6

Please run this other one, do you get an organizational chart?

VBA Code:

Sub another_test()
Dim oshp
Set oshp = ActiveSheet.Shapes.AddSmartArt(Application.SmartArtLayouts _
("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart"))
End Sub

 

Z

zach2025

New Member

Joined

Sep 14, 2022

Messages

4

Office Version

1. 2016

Platform

1. Windows

• Sep 14, 2022

• #7

Hey Worf,
I am also trying to get this Org Chart to work. I am on Excel 2016, and running the test above gives me an Org Chart with simplified text. However I think I am missing some key information about how to run the Module. Such as:

Does the primary data go in a sheet named tdata?
What goes in the sheet secdata?
What goes in sheet fshap?

Even taking your test data above, and putting it into tdata, or in tdata and secdata, comes up with an error.

If it is any help I get a type mismatch error on the following line:
"arr = Range([a1].CurrentRegion.Address) "

 

Z

zach2025

New Member

Joined

Sep 14, 2022

Messages

4

Office Version

1. 2016

Platform

1. Windows

• Sep 14, 2022

• #8

Got it to work once, when I put the data in fshap like you had in the example, but now it isn't working. I keep having an error:

Run-time error '-2147024809 (80070057)':
The item with the specified name wasn't found.

Upon debug this error corresponds to:
ws.Shapes(Range("a" & i) & "aux").Top = sn.Top + sn.Height

 

Z

zach2025

New Member

Joined

Sep 14, 2022

Messages

4

Office Version

1. 2016

Platform

1. Windows

• Sep 14, 2022

• #9

Worf said:

Please run this other one, do you get an organizational chart?

VBA Code:

Sub another_test()
Dim oshp
Set oshp = ActiveSheet.Shapes.AddSmartArt(Application.SmartArtLayouts _
("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart"))
End Sub

Click to expand...

Got this to work, and I got the code to work once, but now it isn't working. Any advice? Running 16, and this code works as well.

 

Worf

Well-known Member

Joined

Oct 30, 2011

Messages

4,252

• Sep 26, 2022

• #10

Were you able to proceed? you can also try post #33 from the thread below.

VBA to create organisation chart

Hi there, I found a great macro on the web that nearly does my job. But only nearly. Unfortunately this code is really high level, and I have no idea how to solve it. Super challenging! You can download the file here: orgaproblem And what I would like to achieve is below: (see picture) Note...

www.mrexcel.com