Linking connectors by a larger connector

[Doflamingo]

Hi all,

I'm trying to find the lines of code to link the little connectors to a single one.

Here is what I have currently, where the cells above represent the items b of a listbox 2, with a connector for each item, then below there is a cell that contain the value of a textbox.

Currently what I have with the condition that my list box 2 contains 5 items

https://www.dropbox.com/s/uvg353oywi...iddle.png?dl=0

and the goal is to find how to link connectors by a signe one and make appear a little one which would link the big one with the cells containing the text box value

https://www.dropbox.com/s/rby1bdc0u8...20for.png?dl=0

Here is the code of the listbox2 that allows me to displays all the items of the listbox 2 in the same row, with a connector for each item

Dim orig As Range, dest As Range, b%, con As Shape
For b = 0 To ListBox2.ListCount - 1
With Cells(5, b * 2 + 3)
.ColumnWidth = 15
.Value = ListBox2.List(b)
.BorderAround
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
End With

Set orig = Cells(5, b * 2 + 3)
Set dest = Cells(12, b * 2 + 3)
Set con = ActiveSheet.Shapes.AddConnector(1, orig.Left + orig.Width / 2, _
orig.Top + orig.Height, dest.Left + dest.Width / 2, dest.Top)
con.Line.Weight = 1
con.Line.ForeColor.RGB = RGB(0, 0, 0)
Set orig = orig.Offset(, 2)
Set dest = dest.Offset(, 2)

I think I should remove that line
Set dest = Cells(12, b * 2 + 3)
because it decides the lenght of the connectors

And here is the code of the cell containing the textbox value

qty = WorksheetFunction.RoundUp((ListBox2.ListCount * 2 - 1) / 2, 0)
With Cells(10, qty + 2)
.ColumnWidth = 15
.Value = TextBox1.Value
.BorderAround
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
End With


Any ideas ?

 

[Worf]

Would you consider using shapes? A smart art will automatically provide the connectors.

 

[Doflamingo]

Hi Worf,

I'm open to any ideas .

Currently I'm studying what Logit in the thread
Creating an organizational chart in Excel

gave me. His excel file is impressive, but as I can see, horizontal connector exist with his example of Tree chart, here the screenshot

https://www.dropbox.com/s/da060qbbf7anio8/Untitled.png?dl=0

and here the link to download his excel file

[url]https://www.amazon.com/clouddrive/sh...lHftVMsJpVqqqd[/URL]

That's almost what I would like to create, but with cells, like we have begun if it's possible, given that the the userform has been created and the project is moving on.

 

[Doflamingo]

I'm going to share the file with Dropbox, maybe it will be easier to understand how items of the different listboxes interacts with textbox values and combobox value on the excel sheet

 

[Doflamingo]

And how connectors must interact btw the different cells filled.

 

[Doflamingo]

Here is the thread with the userform

[h=1]Structure chart with Userform - Link little connectors by a larger and single connector[/h]

 

[Doflamingo]

Does anyone have heard about that line ?

But for me I will have to replace Dembo and Demfo by the 1st connector and the last one on the row 5... Any ideas

AddConnectorBetweenShapes msoConnectorStraight, ActiveSheet.Shapes("DEMDO"), ActiveSheet.Shapes("DEMFO")

 

[Doflamingo]

Hi all, any chances ?

I've found this

Sub AddLine()
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long
l1 = Range("Start").Left
l2 = Range("Start").Top + Range("Start").RowHeight
r1 = Range("Stop").Left
r2 = Range("Stop").Top
With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
.ForeColor.RGB = RGB(0, 0, 0)
End With
End Sub

The code works for cells renamed start and stop


But I don't know how to replace ''Start'' and ''stop'' in my lines of code...

Any idea ?

 

[Doflamingo]

Hi all,

I've tried this code

[FONT=&quot]Sub AddRedLine()[/FONT]
[FONT=&quot]Dim ws As Worksheet[/FONT]
[FONT=&quot]Dim s As Shape[/FONT]
[FONT=&quot]Set ws = ActiveSheet[/FONT]
[FONT=&quot]Set s = ws.Shapes.AddLine(30, 10, 100, 50)[/FONT]
[FONT=&quot]s.Line.ForeColor.RGB = RGB(255, 0, 0)[/FONT]
[FONT=&quot]End sub
[FONT=&quot]
Where the line starts at (30,10) and ends at (100,50).

[/FONT]But I still don't know how to apply it to my code above with the variable Set dest = Cells(12, b * 2 + 3) that represent connectors of the cells filled with the listbox items.

Any chances to get a point of view ?



[/FONT]

 

[Doflamingo]

Or this also

[FONT=&quot]Set[/FONT][FONT=&quot] conn = w.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)

with the variable ''conn'' that represents the connector

and

[/FONT][FONT=&quot]conn.ConnectorFormat.BeginConnect rect, 1[/FONT]
[FONT=&quot]conn.ConnectorFormat.EndConnect Logo, 1


with the variables rect and the other one logo, but still have problems to apply those lines of code to mine...

any chance to get an answer on this topic ? [/FONT]