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

Doflamingo

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

:confused:
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,947
This code connects the cells; what would you like to do next?

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

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

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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 ...:confused: although the logic seems good ...

anybody, any ideas ? :confused:
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,947
That is C event code, selecting the same item on both list boxes. Here is some drawing VBA:

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

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

Thanks again Worf for your answers !!
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
No sorry, the image is supposed to give that

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

Doflamingo

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

Board Regular
Joined
Apr 16, 2019
Messages
238
I was just thinking...''elbow connectors'' for what I want would be a good solution ?

Any point of view ? :confused:
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,947
To post a screenshot, upload it to a hosting site like Drop Box and paste the link here.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,399
Messages
5,450,170
Members
405,590
Latest member
bal016

This Week's Hot Topics

Top