Worf

Organization chart with VBA

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,261
Worf submitted a new Excel article:

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


Read more about this Excel article...
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
What do you get when running this?

VBA Code:
Sub test()
MsgBox Val(Application.Version)
End Sub
 
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
 
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) "
 
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
 
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.
 
Were you able to proceed? you can also try post #33 from the thread below.

 

Forum statistics

Threads
1,226,269
Messages
6,189,953
Members
453,584
Latest member
daihoctuxaeptit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top