DoubleClick on row in ListBox on UserForm

[Grz3siu]

Hello
In UserForm I have a ListBox, I would like that ListBox’s data source were coming from separate spreadsheet called “test” Address to this spreadsheet would be kept in cell A2 of “data” spreadsheet where ListBox is created on. In addition I need user to be able to double click on UserForm row in order to see message box with value from 1^st cell of it. Screenshots of desired outcome below.

Dim SourceWB As WorkbookDim ListItems As Variant


Application.ScreenUpdating = False


Set SourceWB = Workbooks.Open(Sheets("data").Range("A2"), False, True)


ListItems = SourceWB.Worksheets(2).Range("B1:AM3")
' here I am collecting data directly from the range, I would like it to be fetched from the table named "Table2"
With Me.ListBox1
    .List = ListItems
    .ColumnCount = 5
End With


SourceWB.Close False
Set SourceWB = Nothing


Application.ScreenUpdating = True

Hope you can help!

 

[Grz3siu]

I found a solution, but there is still a small problem, because it returns me 38 times. Below is the code, if someone wants to come close. Write if you can solve the problem with the amount of returned value.

Dim SourceWB As WorkbookDim ListItems As Variant


Application.ScreenUpdating = False


Set SourceWB = Workbooks.Open(Sheets("Secret").Range("CE2"), False, True)


'ListItems = SourceWB.Worksheets(2).Range("Tabela2")
ListItems = SourceWB.Worksheets(2).Range("B1:AM3")
With Me.ListBox1
    .List = ListItems
    .ColumnCount = 38
End With


SourceWB.Close False
Set SourceWB = Nothing


Application.ScreenUpdating = True

and

Dim I As LongDim msg As String
 
    If ListBox1.ListIndex <> -1 Then
        For I = 0 To ListBox1.ColumnCount - 1
            msg = msg & ListBox1.Column(7) & vbCrLf
        Next I
    End If
MsgBox msg

 

[Yongle]

Loop is not required if you only want the message once:

If ListBox1.ListIndex <> -1 Then MsgBox = ListBox1.Column(7) & vbCrLf