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

 

[gmazza76]

Thanks

As i am still learning VB can you point me in the right direct please
I have the place it is stored in file "FYVData.xls" on sheet "Breakdown Stats"

Also can i do this for 3 listboxes in the "Private Sub UserForm_Initialize()" as i need to complete this for 3 seperate departments?

Cheers

Private Sub UserForm_Initialize()
 
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
    
    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]

Private Sub UserForm_Initialize()
'...
End Sub

is code that needs to go in the code module for the userform.
Right-click the userform in the Project explorer and select "View Code". Paste the code over there.

If you then show your userform automatically the other file will be opened, and the listbox will be filled.
Note that you are using an iterative (loop) procedure (by Mike) and not "reading values all at once" as I suggested earlier.
The choice is up to you but I have the impression that you'd better take a step back and read a book on VBA, prior to undertaking these kind of exercises.

 

[gmazza76]

Good Afternoon,

I have been trying to find info on listboxes and the books i have only go into these in small bits.

I have ended up using the code you suggested as below but i get the following error

Run-time error '424' - Object required

Are you able to suggest anything?

Many Thanks

Private Sub UserForm_Initialize()
 
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
'Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
    
    With Stats.Range("B1:I14")
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
End Sub

 

[wigi]

i get the following error

Run-time error '424' - Object required

On what line of the code?

 

[gmazza76]

Afternoon,

As I have it attached to a button to load the userform it defaults back to

Sub Group8_Click()
   
   Figures.Show
   
End Sub

This is the only code i have in the userform

Private Sub UserForm_Initialize()
 
ActiveSheet.AutoFilterMode = False
 
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
'Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
    
    With Stats.Range("B1:I14")
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
 
Dim Today
    
Today = Format(Now, "dd/mm/yyyy") 'Enters todays date
Me.TextBox1.Value = Today
Me.TextBox1.Locked = True
End Sub

 

[wigi]

Sorry but I still do not see an answer to my earlier question.

 

[gmazza76]

Sorry,

I think i have found the line it has the "Run-time error'424': Object Required".

The VB line it comes up on is

Private Sub UserForm_Initialize()
 
ActiveSheet.AutoFilterMode = False
 
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
'Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
 
   With Stats.Range("B1:I14")
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
 
Dim Today
 
Today = Format(Now, "dd/mm/yyyy")
Me.TextBox1.Value = Today
Me.TextBox1.Locked = True
End Sub

Cheers