circular flow diagram

[arvin_cool88]

I need to write a VBA script that will take the cells I've chosen and turn them into a circular flowchart.30911

[Ian Crawford]

I am new here, so apologies if I may be doing this wrong. Assuming that the diagram is right, the labelling of the shapes is confusing. Why are they named 1.A, 2.B, 3 .C, 4.D & 5.E? Given that you posted in an Excel forum, if they were cell references then it appears impossible to have them in this order. if they are simply shapes then I believe they would have a property number such as shp01, shp02, etc.

I assume also that the string value 05.01.2015 is a Date, however what do the string values (33,02,238, 52,78682, 52,70,118, 32,92,000 & 85,52,056) represent? I'm sorry if I've read the value incorrectly but I personally find images hard to read at times.

[arvin_cool88]

I appreciate your reply.


The flowchart basically illustrates how money moves between various entities.

A, B, C, D, and E are just the sample names of entities.

Yes, the string value 05.01.2015 is a Date. (not mandatory)

The string values (33,02,238, 52,78682, 52,70,118, 32,92,000 & 85,52,056) represent the amount value.

[arvin_cool88]

Sub CreateFlowchart()
Dim flowchartWS As Worksheet, sourceRange As Range, shp As Shape, i As Integer
Dim shpLeft As Integer, shpTop As Integer, shpWidth As Integer, shpHeight As Integer


Set sourceRange = Selection

Sheets.Add after:=ActiveSheet
Set flowchartWS = ActiveSheet

ActiveWindow.DisplayGridlines = False

'Adding the shapes --------------------------------------------------------------------

'initial position of shapes
shpLeft = 20
shpTop = 50

'Loop through selection
For Each cell In sourceRange
shpWidth = Len(cell.Value) * 10 ' Adjust the multiplier as needed for desired width

' Set a minimum width to avoid very small shapes
If shpWidth < 100 Then
shpWidth = 100
End If

shpHeight = 50 ' fixed height

Set shp = flowchartWS.Shapes.AddShape(msoShapeRectangle, shpLeft, shpTop, shpWidth, shpHeight)

i = i + 1
With shp
.Name = "shp" & i
With .TextFrame
.Characters.Text = cell.Value
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Characters.Font.Size = 12
End With
End With
shpLeft = shpLeft + shpWidth + 20 ' adjust the offset as needed for spacing between shapes
Next cell

'Adding the lines (connectors) --------------------------------------------------------
For n = 1 To i - 1
fromBox = "shp" & n
toBox = "shp" & n + 1
flowchartWS.Shapes.AddConnector(msoConnectorStraight, 20, 20, 20, 20).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.ConnectorFormat.BeginConnect flowchartWS.Shapes(fromBox), 4
Selection.ShapeRange.ConnectorFormat.EndConnect flowchartWS.Shapes(toBox), 2
Next n


End Sub





This code generates the flowchart in a horizontal manner.

[p45cal]

cross posted:
https://chandoo.org/forum/threads/circular-flow-diagram.54098/#post-299119
https://www.mrexcel.com/board/threads/circular-flowchart.1240461/