Circular Flow Diagram

[arvin_cool88]

I need to write a VBA script that will take the cells I've chosen as input and turn them into a circular flowchart. I am also attaching the sample image for your reference.
The attached flowchart basically illustrates how money moves between various entities.
A, B, C, D, and E are just the sample names of entities.
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.

 

[vletm]

arvin_cool88​

Based Your writing
... where do You need VBA?
... You could get something same like this sample shows.
or
If You had other kind of vision,
then You should able to explain much more about Your needs.

 

[arvin_cool88]

I need the VBA in Excel.
Actually, I am thinking that there should be three columns (transfer from, transfer to, and amount).

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.

 

[vletm]

arvin_cool88​

Where did You answer to my question (Where...)?
As well as, I asked to You to explain much more about Your needs.
You seems to skip both of those.

Please, reread Forum Rules
- there are many sentences, which could enable You to get answers:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[arvin_cool88]

I asked to You to explain much more about Your needs.
You seems to skip both of those.

So the problem is that I have an Excel file that consists of three columns: Transfer from, Transfer to, and Amount. And I want to use a flowchart to show the three-column data.

In order to generate a flowchart in an Excel worksheet depending on a chosen range of cells, I want to create a VBA macro.

The flowchart should be designed with a circular layout.

The connecting lines' lengths should be flexible and ensure that they touch the shapes.

The dimensions of the shape must be dynamic based on the number of characters.

The amount listed in the Amount column ought to lie on or over the connecting line.

 

[vletm]

arvin_cool88​

You've the problem ... Could below be a solution?

or could You modify Your problem?

 

[arvin_cool88]

I'm fine with your solution if the one I'm asking for can't be done.

 

[vletm]

Sheet2 is ... fine.

 

[arvin_cool88]

I sincerely appreciate your work.

Yes, Sheet 2 is fine, however there are a few adjustments that need to be made.

1. If characters in the To and From columns that are longer than 15 characters cannot be seen in the rectangle.

2. If there are two arrows pointing in the same rectangle, only one value is displayed.

3. It does not display if the value in the Value column is '20+10=30'.

 

[vletm]

arvin_cool88​

I can test only with Your given sample datas.
There could be whatever even unwanted result with other sample data.
#1 one solution - keep those short - there are few possible solutions, but there will be always limits
#2 that's unwanted result - there are few possible solutions
#3 it tries to display, I tested with '20+10=30' - one solution - keep values short

 

[arvin_cool88]

I have tried manually adjusting the text box's and rectangle's dimensions, and then the entire text appears. Can we set the text box's and shape's dimensions according to the number of characters?

 

[vletm]

... hmm?
Now You've plan to do something manually
... should I ask again as I've asked where do You need VBA?
Your Can we ... I won't do that kind of settings.
Did You read my previous writings?
... eg there will be always limits
I modified Your #2 note - there can see both two values ... based You data.
... if there will be more than two values like those - then there will be a challenge.
Have You tried to read my code?
... there has been a feature, how to set some settings ... manually.
... ... I modified that feature
... ... ... all widths and heights depends on cells F1 width and height
I'd to add one more checking
... the 1st and the last 'points'-text have to be same.

 

[p45cal]

cross posts:

circular flow diagram

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

www.vbaexpress.com

circular flowchart

I want to make a circular flowchart diagrams in Excel. I am new to this thing. I don't know how to make it. Can someone teach me how to make these kinds of charts? I am sharing a screenshot for your reference.

www.mrexcel.com

 

[vletm]

arvin_cool88​

Please reread Forum Rules - those are for You too:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[arvin_cool88]

... hmm?
Now You've plan to do something manually
... should I ask again as I've asked where do You need VBA?
Your Can we ... I won't do that kind of settings.
Did You read my previous writings?
... eg there will be always limits
I modified Your #2 note - there can see both two values ... based You data.
... if there will be more than two values like those - then there will be a challenge.
Have You tried to read my code?
... there has been a feature, how to set some settings ... manually.
... ... I modified that feature
... ... ... all widths and heights depends on cells F1 width and height
I'd to add one more checking
... the 1st and the last 'points'-text have to be same.

Now You've plan to do something manually - No, I was just checking if the value appeared by adjusting the size, then we could change the size in the code.
Could you please explain me what do you meant by where do You need VBA?
Did You read my previous writings? eg there will be always limits - Yes
I modified Your #2 note - there can see both two values ... based You data. - The above data is just a sample data. In real the value is sometimes 7- 10 digits long.
I'd to add one more checking... the 1st and the last 'points'-text have to be same. - No no, its not always the same

 

[vletm]

arvin_cool88​

I see ...
... You like many times that You used cross-posting - I'm thinking opposite way.
... You just checking to do manually ... keep on doing it - then You won't need VBA.
... even a sample data should be something as in normal use.
... if You remember - You've been interesting in about circular-flow-diagram ... then the start and the end points should be same.
Take care.

 

[arvin_cool88]

arvin_cool88​

I see ...
... You like many times that You used cross-posting - I'm thinking opposite way.
... You just checking to do manually ... keep on doing it - then You won't need VBA.
... even a sample data should be something as in normal use.
... if You remember - You've been interesting in about circular-flow-diagram ... then the start and the end points should be same.
Take care.

I have mentioned in my writing
The flowchart should be designed with a circular layout.
The connecting lines' lengths should be flexible and ensure that they touch the shapes.
The dimensions of the shape must be dynamic based on the number of characters.