Table on a suerform

[gmazza76]

Evening,

I am trying to create a userform to show statistics that are pulled form another spreadsheet.

Is there anyway i can create a table on a userform that will look neat as well as allow me to pull information into it when it is opened via a button?

Many thanks
Gavin

 

[mikerickson]

A ListBox allows you to display things in columns.

 

[gmazza76]

Good Afternoon,

I am fairly new to VB.
The "listbox" that you suggested can it show the info i have in another spreadsheet.

I need the form to show exactly what the spreadsheet says ( 4 columns & 9 Rows ) It should show how many queries are open on a team etc..

Is there anywhere i can look up how to create a listbox etc....

Many thanks
Gavin

 

[mikerickson]

Make a userform with a list box and put this code in the userform's code module

Private Sub UserForm_Initialize()
    Dim rangeToShow As Range, oneRow As Range
    Dim i As Long
    Set rangeToShow = Sheet1.Range("A1:D7")
    
    With ListBox1
        .ColumnCount = rangeToShow.Columns.Count
        For Each oneRow In rangeToShow.Rows
            .AddItem oneRow.Cells(1, 1).Text
            For i = 1 To .ColumnCount - 1
                .List(.ListCount - 1, i) = oneRow.Cells(1, i + 1).Text
            Next i
        Next oneRow
    End With
End Sub

 

[wigi]

Mike,

Or without looping:

Private Sub UserForm_Initialize()
    With Sheet1.Range("A1:D7")
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
End Sub

or if the columncount is changed at design time:

Private Sub UserForm_Initialize()
    ListBox1.List = Sheet1.Range("A1:D7").Value
End Sub

 

[mikerickson]

My Mac gets unstable with that approach, hence the loop.

 

[wigi]

My Mac gets unstable with that approach, hence the loop.

Reading 28 cells?

 

[mikerickson]

Any multi column list box, there are odd instabilities in Mac Excel/VBA.
(Another example, put an event in ThisWorkbook, OK,
remove the code from the event leaving the two lines, Private Sub... & End Sub, OK,
remove those two lines, crash)

Does the multi-column ListBox have the appearance and features that gmazza76 wants?

 

[gmazza76]

Table on a userform

AS my data is in another workbook, would i still be able to do this?

 

[wigi]

Re: Table on a userform

AS my data is in another workbook, would i still be able to do this?

You can easily change the sheet references in my or Mike's code.