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:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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:
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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 !!
 
Upvote 0
No sorry, the image is supposed to give that

b b b b b b b
| | | | | | |
-----------------------
|
Textbox.value
 
Upvote 0
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
 
Upvote 0
I was just thinking...''elbow connectors'' for what I want would be a good solution ?

Any point of view ? :confused:
 
Upvote 0
To post a screenshot, upload it to a hosting site like Drop Box and paste the link here.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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
Back
Top