# Linking connectors by a larger connector

#### Doflamingo

##### Board Regular
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 ?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Worf

##### Well-known Member
Would you consider using shapes? A smart art will automatically provide the connectors.

#### Doflamingo

##### Board Regular
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

[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

##### Board Regular
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

##### Board Regular

And how connectors must interact btw the different cells filled.

#### Doflamingo

##### Board Regular

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

#### Doflamingo

##### Board Regular
Hi all, any chances ?

I've found this

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
.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

##### Board Regular
Hi all,

I've tried this code

[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

##### Board Regular
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]

Replies
2
Views
300
Replies
2
Views
236
Replies
10
Views
593
Replies
2
Views
274
Replies
13
Views
318

1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

### 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.

### Which adblocker are you using?

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

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