Linking connectors by a larger connector

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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 ? :confused:
 

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
Joined
Oct 30, 2011
Messages
4,139
Would you consider using shapes? A smart art will automatically provide the connectors.

OmC4KUs.jpg
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

Board Regular
Joined
Apr 16, 2019
Messages
238
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
Joined
Apr 16, 2019
Messages
238

ADVERTISEMENT

And how connectors must interact btw the different cells filled.
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238

ADVERTISEMENT

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 :confused:

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

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

Board Regular
Joined
Apr 16, 2019
Messages
238
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 ? :confused:



[/FONT]
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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 ? :confused:[/FONT]
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top