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

 

[Norie]

You can set the ColumnWidths property.

For a 4 column list box with 2 columns 25 pts wide and 2 columns 40pts wide:

ColumnWidths = "25 pt; 25 pt; 40 pt; 40 pt"

You could do that in design view or in code - you'll need to play about with the widths to get it looking like you want.

 

[gmazza76]

Thanks for the reply.

I have tried entering it into the code, but it doesnt seem to work.
Can you advise if the way i am doing it is correct

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
Application.ScreenUpdating = False
 
Set wbData = Workbooks.Open(Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\Data.xls")  'Home
'Set wbData = Workbooks.Open(Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\Data.xls")
 
Set wsStats = wbData.Worksheets("Stats")
   With wsStats.Range("A2:H17")
        ColumnWidths = "25 pt; 25 pt; 40 pt; 40 pt"
        Figures.ColumnCount = .Columns.Count
        Figures.List = .Value
   End With
 
Me.TextBox1.Value = Format(Now, "dd/mm/yyyy")
Me.TextBox1.Locked = True
 
End Sub

Many Thanks