Listbox Issue

[gmazza76]

Good Afternoon,

I am having an issue loading a userform with a listbox on.

It keeps giving the VB issues as "Run-time error'424': Object Required".

I think the line of code it is saying is the issues is the one below in RED.

Any help would be appreciated as the project is nearly done.

Many Thanks

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

 

[VoG]

What is Stats? It doesn't seem to be defined?

 

[Norie]

If Stats is the name of a worksheet in the workbook being opened you can use this.

Private Sub UserForm_Initialize()
 Dim wbData As Workbook
Dim wsStats As Worksheet
ActiveSheet.AutoFilterMode = False
 
Set wbData = 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"
 
Set wsStats = wbData.Worksheets("Stats")

   With wsStats.Range("B1:I14")
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
 

Me.TextBox1.Value = Format(Now, "dd/mm/yyyy")

Me.TextBox1.Locked = True

End Sub

 

[gmazza76]

"Stats" is a sheet in "FYVData.xls" that holds the data that i want to pull into the userform.

What do i need to alter?

Cheers

 

[Norie]

So Stats is a worksheet in the workbook (FYVData.xls) you are opening and you want to populate the listbox with data from it?

If that's the case you don't need to alter anything at all in the code I posted.

 

[gmazza76]

Afternoon,

Thanks for the VB, but it seems to debug still but at

ListBox1.List = .Value
ListBox1.ColumnCount = .Columns.Count

Cheers
Gavin

 

[Norie]

Gavin

Please tell us how it 'debugs' and post the exact code that it is causing the problem.

 

[gmazza76]

All info as requested

As i step through the code the line it debugs at is as follows

ListBox1.ColumnCount = .Columns.Count
ListBox1.List = .Value

and the error "Run-time error '424': Object required"

The code i am using is as below.

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

 

[Norie]

Is your listbox called ListBox1?

 

[gmazza76]

Sorry, due to the question you sent i have realised i didnt address the listbox properly.

Is there anyway i can alter the width of the columns that appear in the listbox?

Many Thanks