View Full Version : circular flow diagram
arvin_cool88
07-04-2023, 01:01 AM
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
07-04-2023, 08:51 PM
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
07-05-2023, 11:45 PM
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
07-07-2023, 04:35 AM
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
07-11-2023, 12:55 PM
cross posted:
https://chandoo.org/forum/threads/circular-flow-diagram.54098/#post-299119
https://www.mrexcel.com/board/threads/circular-flowchart.1240461/
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.