Listbox format data in list

[Bandito1]

Hi all,

i use the following code to get data in my listbox;

Private Sub UserForm_Initialize()
Dim lindex&
'Puts the user form in the middle of the Excel screen when opened.
    Me.StartupPosition = 0
    Me.Top = (Application.Height / 2) - (Me.Height / 2)
    Me.Left = (Application.Width / 2) - (Me.Width / 2)
Me.lstSearchResults.ColumnCount = 4
Me.lstSearchResults.List = Sheets("BMRcloset").[F7:I37].Value
With Me.lstSearchResults
.List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mmm/yy"))
End With
End Sub

Now only the first data in the listbox is formatted right.
How can i make it that all the data is formatted right?

 

[Micron]

try adding
Dim i As Integer
Then after Me.Left line

With Me.lstSearchResults
    .ColumnCount = 4
    .lstSearchResults.List = Sheets("BMRcloset").[F7:I37].Value
    For i = 0 To .ListCount - 1
        .List(lindex, i) = (Format((.List(lindex, i)), "dd/mmm/yy"))
    Next
End With

 

[Bandito1]

Thanks for your reply.

I made this from it;

Private Sub UserForm_Initialize()
Dim lindex&
Dim i As Integer
'Puts the user form in the middle of the Excel screen when opened.
    Me.StartupPosition = 0
    Me.Top = (Application.Height / 2) - (Me.Height / 2)
    Me.Left = (Application.Width / 2) - (Me.Width / 2)

With Me.lstSearchResults
    .ColumnCount = 4
    .lstSearchResults.List = Sheets("BMRcloset").[F7:I37].Value
    For i = 0 To .ListCount - 1
        .List(lindex, i) = (Format((.List(lindex, i)), "dd/mmm/yy"))
    Next
End With
End Sub

It gives the error; compile error: Method or data member not found.
It highlights .lstSearchResults in the with me part.

 

[dmt32]

Hi,
assuming that your data is formatted as required in the ranges then see if this update to your code will do what you want

Private Sub UserForm_Initialize()
    Dim r             As Long, c As Long
    Dim arr()       As Variant
    Dim rng         As Range
   
    Set rng = ThisWorkbook.Worksheets("BMRcloset").Range("F7:I37")
   
    ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
   
    For r = 1 To UBound(arr, xlRows)
        For c = 1 To UBound(arr, xlColumns)
            arr(r, c) = rng.Cells(r, c).Text
        Next c
    Next r
   
    'Puts the user form in the middle of the Excel screen when opened.
    Me.StartupPosition = 0
    Me.Top = (Application.Height / 2) - (Me.Height / 2)
    Me.Left = (Application.Width / 2) - (Me.Width / 2)
   
    With Me.lstSearchResults
        .ColumnCount = UBound(arr, xlColumns)
        .List = arr
    End With
   
End Sub

Dave

 

[Bandito1]

That works perfectly!

Now i have this problem, maybe you know.

Does this code fit in;

Private Sub UserForm_Initialize()
Dim Ary as variant
'Puts the user form in the middle of the Excel screen when opened.
    Me.StartupPosition = 0
    Me.Top = (Application.Height / 2) - (Me.Height / 2)
    Me.Left = (Application.Width / 2) - (Me.Width / 2)

   Ary = Sheets("BMR data").Range("A3", Sheets("BMR data").Range("A" & Rows.Count).End(xlUp).Offset(, 22)).Value
   With Me.lstSearchResults
      .ColumnCount = 22
      .ColumnWidths = "0;100;0;0;0;0;0;0;0;100;0;0;100;0;0;100;0;0;100;0;0;1"
      .List = Ary
   End With

With this code i can exclude columns from my listbox.

Is it possible to use your solution in above code?

 

[dmt32]

That works perfectly!

Glad suggestion resolved your issue

Now i have this problem, maybe you know.

This is a different question & should post in new thread

Dave