Load from a spreadsheet into a userform

[gmazza76]

Afternoon,

I need to load about 6 sets of numbers onto a userform when the stats userform is loaded to give an overview to managers.

I am using the code below but it debugs out.
I have tried stepping into the code but it isnt showing anything.

Please help
Many Thanks
Gavin

'enters the date on the form
Private Sub userform_Initialize()
Dim Today
Today = Format(Now, "dd/m/yyyy")
Me.Today.Value = Today
Me.Today.Locked = True
 
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home

On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0
    
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Set ws = Worksheets("Breakdown Stats")
            FYVOverview.Show False
            FYVOverview.TextBox1.Value = Cells(C3)
            FYVOverview.TextBox7.Value = Cells(C4)
            FYVOverview.TextBox6.Value = Cells(C5)
 
 
End Sub

 

[RoryA]

I assume the last bit was meant to be:

            FYVOverview.TextBox1.Value = Range("C3").value
            FYVOverview.TextBox7.Value = Range("C4").value
            FYVOverview.TextBox6.Value = Range("C5").value

?

 

[gmazza76]

Afternoon,

I have amended as you suggested but I keep getting an error as follows

Run-time error '1004':
Application-defined or object-defined error

Any idea what this means?

 

[Ruddles]

Is sheet "Breakdown Stats" in ThisWorkbook or "FYVData.xls"? Better to make it unequivocal and code Set ws = ThisWorkbook.Worksheets("Breakdown Stats") or Set ws = myBook.Worksheets("Breakdown Stats") as appropriate.

Also, consider where the data is coming from when you referer to Range("C5"). Should that be ws.Range("C5")?

What does "debugs out" mean? What's the error message, and on which line does it occur?

 

[gmazza76]

All the information is being pulled from FYVData.xls

It errors when it tries to pull the data in.
If i click ok it loads the userform but blank.

Cheers

 

[RoryA]

To repeat Ruddles' question: "What's the error message, and on which line does it occur?"

 

[gmazza76]

Sorry the error message is as follows

"Run-time error '1004':
Application-defined or object-defined error"

When i step through it it gives the error above and then just loads the form when i click ok.

I have amended the code as per below

Dim myBook As Workbook
 
On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0
    
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Set ws = myBook.Worksheets("Breakdown Stats")
            FYVOverview.Show False
            FYVOverview.TextBox1.Value = ws.Range(C3).Value
            FYVOverview.TextBox7.Value = ws.Range(C4).Value
            FYVOverview.TextBox6.Value = ws.Range(C5).Value

End Sub

 

[RoryA]

That's not the code I posted. The range addresses need to be in quotes as in my example.

 

[gmazza76]

Sorry about this.

I am fairly new to VB.

Which part is it i need to amend.
Do i need to amend the ws.range part as i have set the WS as per your prvious post as the info is being pulled from another sheet.

Cheers

Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Set ws = myBook.Worksheets("Breakdown Stats")
            FYVOverview.Show False
            FYVOverview.TextBox1.Value = ws.Range(C3).Value
            FYVOverview.TextBox7.Value = ws.Range(C4).Value
            FYVOverview.TextBox6.Value = ws.Range(C5).Value

End Sub

 

[RoryA]

FYVOverview.TextBox1.Value = ws.Range("C3").Value

and similarly for the other lines.