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 ?

 

[Worf]

This code connects the cells; what would you like to do next?

Private Sub CommandButton1_Click()
Dim a%, con
For a = 0 To ListBox1.ListCount - 1
    With Cells(12, a * 2 + 3)
        .Value = ListBox1.List(a)
        .BorderAround
        .HorizontalAlignment = xlCenter
        .Borders.Weight = 3
        If a < ListBox1.ListCount - 1 Then
            Set con = ActiveSheet.Shapes.AddConnector(1, .Offset(, 1).Left, _
            .Top + .Height / 2, .Offset(, 2).Left, .Top + .Height / 2)
            con.Line.Weight = 2
        End If
    End With
Next
End Sub

 

[Doflamingo]

Hi Worf, thanks a lot for your answer

Your code is better than mine, shorter and the connectors are well linked to the cells representing each items contained in the listbox

Well, I've applied your code to 2 differents listboxes {listbox 1 (with items a) and listbox 2 (with items b) }

What I would like to understand is how to link the items "a" coming from the listbox 1 TO the items "b" coming from the listbox 2, with this time vertical connectors and not horizontal. It would give something like that in an excel sheet.

a a a a
| | | |
b b b b

and not something like that a-a-a-a

b-b-b-b

Here is the code for listbox 1

Private Sub CommandButton5_Click()
Dim a%, con
For a = 0 To ListBox1.ListCount - 1
With Cells(12, a * 2 + 3)
.ColumnWidth = 15
.Value = ListBox1.List(a)
.BorderAround
.HorizontalAlignment = xlCenter
.Borders.Weight = 3
If a < ListBox1.ListCount - 1 Then
Set con = ActiveSheet.Shapes.AddConnector(1, .Offset(, 1).Left, _
.Top + .Height / 2, .Offset(, 2).Left, .Top + .Height / 2)
con.Line.Weight = 1
con.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End With
Next
End Sub


and here is the code for listbox 2 with items b

Private Sub CommandButton6_Click()
Dim b%, con
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
If b < ListBox2.ListCount - 1 Then
Set con = ActiveSheet.Shapes.AddConnector(1, .Offset(, 1).Left, _
.Top + .Height / 2, .Offset(, 2).Left, .Top + .Height / 2)
con.Line.Weight = 1
con.Line.ForeColor.RGB = RGB(0, 0, 0)
End If
End With
Next
End Sub

Any ideas ?

 

[Doflamingo]

I found this on Internet

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
int i=0;
while (listBox1.SelectedItem != listBox2.Items)
i++;
listBox2.SelectedIndex = i;
}

It received good comments, but absolutely doesn't understant how it works ... although the logic seems good ...

anybody, any ideas ?

 

[Worf]

That is C event code, selecting the same item on both list boxes. Here is some drawing VBA:

Sub Vert()
Dim orig As Range, dest As Range, i%, con As Shape
For i = 0 To ListBox1.ListCount - 1
    Set orig = Cells(5, i * 2 + 3)
    Set dest = Cells(12, i * 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 = 3
    con.Line.ForeColor.RGB = RGB(125, 25, 25)
    Set orig = orig.Offset(, 2)
    Set dest = dest.Offset(, 2)
Next
End Sub

 

[Doflamingo]

Hi Worf, Thanks that's impressive , it works perfectly .

Actually my goal has changed a little bit, the goal is to link the items (variable b) of the listbox 2 with the value of a textbox, I have post an other thread called [h=1]Finding the location of a specific cell[/h]
Because then, when I will find the answer, the goal will be to obtain that:

b b b b b b b etc.
| | | | | | |
---------------------
|
Textbox.value

Sorry for the image, I do what I can given that We can't put any screenshot ...

Any ideas ?

Thanks again Worf for your answers !!

 

[Doflamingo]

No sorry, the image is supposed to give that

b b b b b b b
| | | | | | |
-----------------------
|
Textbox.value

 

[Doflamingo]

Forget it...., I don't succeed to display very well what I'm looking for.

But, it would be a connector for each items b of the listbox 2, a giant connector which would link them all and then a little connector which would link the giant connector to the cell where would be displayed the textbox.value

 

[Doflamingo]

I was just thinking...''elbow connectors'' for what I want would be a good solution ?

Any point of view ?

 

[Worf]

To post a screenshot, upload it to a hosting site like Drop Box and paste the link here.