Userform and retrieving data from listboxes

[erinbe]

Hello,

I've got a user form with 2 listboxes - The first box populates a listing of names of worksheets. When clicking on one name then a button I would like to populate the data from that sheet into a second list box (about 10 rows and 12 columns of data only)

When changing the name of listbox1 then clicking show I'd like that data to change...

I'm new and can't seem to figure this out - I'm sure its an easy syntax that I can't quite get...Please help... This is what I have so far...

Private Sub UserForm_Initialize()
   
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible = xlSheetVisible Then
        ListBox1.AddItem ws.Name
    End If
Next ws

TextBox1 = ListBox1.Value
    
End Sub

Private Sub ListBox1_Click()

Label1.Caption = Sheets(ListBox1.Value).Range("A1").Value

End Sub


Private Sub CommandButton1_Click()
 Dim ws As Worksheet

    With ListBox2
    Sheets(ListBox1.Value).Select  [B][SIZE="4"] (is this line right?)
   
(where do I go from here?)[/SIZE][/B]
        


    End With
End Sub

 

[Andrew Poulsom]

This worked for me:

Private Sub CommandButton1_Click()
    ListBox2.List = Worksheets(ListBox1.Value).Range("A1:T10").Value
End Sub

 

[erinbe]

That's great and so easy ! Thank You...

Is there a way to format the data I'm posting? Some are percentages and times...but they all display as decimal numbers.

 

[Andrew Poulsom]

Try:

Private Sub CommandButton1_Click()
    Dim r As Long
    Dim c As Long
    With Worksheets(ListBox1.Value).Range("A1:T10")
        ListBox2.List = .Value
        For r = 1 To .Rows.Count
            For c = 1 To .Columns.Count
                ListBox2.List(r - 1, c - 1) = .Cells(r, c).Text
            Next c
        Next r
    End With
End Sub

 

[erinbe]

That is great! Thank you so much!!

Why does this work and not the previous? Because it's taking the actual value of each cell?

 

[Andrew Poulsom]

The Text property returns the value as it appears in the cell. The Value property returns the unformatted value.