Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ?


  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    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 ?

  4. #4
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    I found this on Internet

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

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

    anybody, any ideas ?

  5. #5
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    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 Finding the location of a specific cell

    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 !!

  7. #7
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    No sorry, the image is supposed to give that

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

  8. #8
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    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

  9. #9
    Board Regular
    Join Date
    Apr 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

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

    Any point of view ?

  10. #10
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to link items from a listbox 1 to items coming from a listbox 2

    To post a screenshot, upload it to a hosting site like Drop Box and paste the link here.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •