Thread: Userform's listbox values depending on values on certain matrix

Bump no response

What exactly do you want to display in ListBox2? Say Wed is chosen in ListBox1 do you want ListBox2 to contain 5,7,11 or w2,w3,w4?

Remove the RowSource of ListBox2 in the Properties. Then use the Click event of ListBox1

Private Sub ListBox1_Click()
    Dim rng    As Range
    Dim cl     As Range
    Dim R      As Long
    R = Me.ListBox1.ListIndex + 2
    With Sheet2
        Set rng = .Range(.Cells(R, 2), .Cells(R, 6))
    End With
    With Me.ListBox2
        .Clear
        For Each cl In rng
            If Not IsEmpty(cl) Then .AddItem "w" & cl.Value
        Next cl
    End With
End Sub

Originally Posted by Juhanen II

Thanks Roy. However, there's a mistake in the code:

For example if Wed is chosen in Listbox1 , instead of w2,w3,w4 ListBox2contains w5, w7, w11. (So instead of headings of the matrix, "w"+nonblanc values of the specified row are selected to Listbox2)

There's no mistake in the code, it does exactly what I thought that you wanted. However, I think you want the header row so I have changed the code

Private Sub ListBox1_Click()
    Dim rng    As Range
    Dim cl     As Range
    Dim R      As Long
    R = Me.ListBox1.ListIndex + 2
    With Sheet2
        Set rng = .Range(.Cells(R, 2), .Cells(R, 6))
    End With
    With Me.ListBox2
        .Clear
        For Each cl In rng
            If Not IsEmpty(cl) Then .AddItem Cells(1, cl.Column).Value
        Next cl
    End With
End Sub

Secondly, I would be thankful if the code would use range names when referring to ranges, so that I could use the same code in different matrixes.

-Juhanen-

You will need to adapt the code to suit whatever ranges you want to use

It works fine in the example workbook that you uploaded.


Try this slight amendment

Private Sub ListBox1_Click()
 Dim rng    As Range
    Dim cl     As Range
    Dim R      As Long
    R = Me.ListBox1.ListIndex + 2
    With Sheets("Data")
        Set rng = .Range(.Cells(R, 2), .Cells(R, 6))
           End With
    With Me.ListBox2
        .Clear
        For Each cl In rng
            If Not IsEmpty(cl) Then .AddItem Sheets("Data") _
            .Cells(1, cl.Column).Value
        Next cl
    End With
End Sub