How to link items from a listbox 1 to items coming from a listbox 2

[Doflamingo]

Hi all, I'm currently looking the lines of code to link different items coming from 2 different listboxes.

Here is my code that allows to, at least, link items of a single list between them:

Here is the macro applied to the commandbutton that displays the items of the listbox in an excel sheet and add a CONNECTOR between the different items of the listbox. BUT the connector is not linked to the cell of the excel sheet where the items are displayed (1 item = 1 cell of an excel sheet)

Private Sub CommandButton3_Click()


Dim a As Long


For a = 0 To ListBox1.ListCount - 1
With Cells(12, a * 2 + 3)
.Value = ListBox1.List(i)
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If a < ListBox1.ListCount - 1 Then
Set Con = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, .Offset(, 1).Left, .Height * 4.5, .Offset(, 2).Left, .Height * 4.5)
Con.Line.Weight = 2
End If
End With
Next a


For a = 0 To ListBox1.ListCount - 1
Cells(12, a * 2 + 3) = ListBox1.List(a)

Cells(12, a * 2 + 3).Select

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With



Next a






End Sub

Any ideas ?

 

[Doflamingo]

Thanks a lot Worf for your advice, you learn me much more than I expected !!

So I've dropped a screenshot of what I expect with the items b of my listbox 2 and the connectors which are linked to the cell that contains the textbox value

https://www.dropbox.com/home/Screen...ot+Listbox2+items+linked+to+textbox.value.png

Let me know if anybody can visualize it

 

[Doflamingo]

Hello Worf, thanks a lot, you learn me more than I expected.

Here is the screenshot of what I'm looking for with dropbox
https://www.dropbox.com/home/Screen...ot+Listbox2+items+linked+to+textbox.value.png

With the items b of the listbox2, linked to a a cell that contains the textbox value .

Here is the code you gave me a little bit modified

Private Sub CommandButton9_Click()
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)
Next
End Sub

That allows me to represent things like that

https://www.dropbox.com/home/Screenshots?preview=Untitled.png

 

[Doflamingo]

here is the screenshot of what I'm looking for

https://www.dropbox.com/home/Screenshots?preview=What+I+want.png

sorry, the link was not well recorded previously.

 

[Worf]

I cannot access your file. Inside Drop Box, you must choose share, and then copy link.

 

[Doflamingo]

Ah sorry, 1st time I used Dropbox.

Here normally is the link of what I have currently

https://www.dropbox.com/s/vv1hrndfo2mftvu/What I have.png?dl=0

And here is the link of what I want

https://www.dropbox.com/s/9nu9ypwopwudbgl/What I want.png?dl=0

Don't hesitate to let me know if the contents don't display.

Thanks again Worf !

 

[Doflamingo]

Curently, before to add the connector, I'm looking the lines of code which would allow me to find the exact location of the cell where would be displayed the text.box value. it means below the others cells representing all the items b of the listbox2 and at the exact middle of those ones, but just below

I guess, given that line of codes represent the location of the different items b of listbox 2
Cells(5, b * 2 + 3)

I can imagine that the location of my textbox would be at least cells(8,...).

 

[Doflamingo]

Here is the code of the command button supposed to find the exact location of the cell containing the value of the textbox

Private Sub CommandButton8_Click()
For b = 0 To ListBox2.ListCount - 1
Cells(8, (b * 2 + 3) / 2).Value = TextBox1.Value
End Sub

but it doesn't work

Any ideas ?

 

[Doflamingo]

Thanks again Worf for your help and your patience.

The problem has been solved in that thread

[h=1]Finding the location of a specific cell[/h]

 

[Doflamingo]

The next step is to link the connectors of each items b of the listbox 2 to the cell containing the value of the textbox.

Here is the new thread

[h=1]Linking connectors by a larger connector[/h]
Thanks again for your advices Worf, I think we can consider that thread over thanks to you