format number(#,##0.00) & date(yyyy/mm/dd) doesn't work in listbox on userfrom

[abdelfattah]

Hello
I have this part of my project

Private Sub LBoxPop()
  Data = ws.Cells(1).CurrentRegion.Value: x = 0
  ReDim Temp(1 To UBound(Data, 1), 1 To 5)
  For i = 1 To UBound(Data)
      x = x + 1
      Temp(x, 1) = Format(Data(x, 1), "yyyy/mm/dd")
       Temp(x, 3) = Format(Data(x, 3), "#,##0.00")
       Temp(x, 4) = Format(Data(x, 4), "#,##0.00")
       Temp(x, 5) = Format(Data(x, 5), "#,##0.00")
      For ii = 1 To 5
        Temp(x, ii) = Data(i, ii)
      Next ii
    'End If
  Next i
  With UserForm1.ListBox1
    .ColumnCount = 5
    .columnWidths = "80;240;120;120;120"
    .List = Temp
  End With
End Sub

I try showing format date in first column in listbox like this 2023/08/06
and formatnumber in column 3,4,5 like this 200,220.00
but doesn't work for me as in code above and bold lines
any help to fix it ,please ?

 

[dmt32]

Hi,
assuming that the ranges in your worksheet are displaying the required formats then see if this update to your code does what you want

Private Sub LBoxPop()
    Dim r          As Long, c As Long
    Dim Data()     As Variant
    Dim rng        As Range
  
    Set rng = ws.Cells(1, 1).CurrentRegion
    ReDim Data(1 To rng.Rows.Count, 1 To rng.Columns.Count)
   
    For r = 1 To UBound(Data, xlRows)
        For c = 1 To UBound(Data, xlColumns)
            Data(r, c) = rng.Cells(r, c).Text
        Next c
    Next r
   
    With UserForm1.ListBox1
        .ColumnCount = 5
        .columnWidths = "80;240;120;120;120"
        .List = Data
    End With
   
End Sub

I am also assuming that your object variable ws has been intialized elsewhere in your project.

Dave

 

[Herakles]

Hello
I have this part of my project

Private Sub LBoxPop()
  Data = ws.Cells(1).CurrentRegion.Value: x = 0
  ReDim Temp(1 To UBound(Data, 1), 1 To 5)
  For i = 1 To UBound(Data)
      x = x + 1
      Temp(x, 1) = Format(Data(x, 1), "yyyy/mm/dd")
       Temp(x, 3) = Format(Data(x, 3), "#,##0.00")
       Temp(x, 4) = Format(Data(x, 4), "#,##0.00")
       Temp(x, 5) = Format(Data(x, 5), "#,##0.00")
      For ii = 1 To 5
        Temp(x, ii) = Data(i, ii)
      Next ii
    'End If
  Next i
  With UserForm1.ListBox1
    .ColumnCount = 5
    .columnWidths = "80;240;120;120;120"
    .List = Temp
  End With
End Sub

I try showing format date in first column in listbox like this 2023/08/06
and formatnumber in column 3,4,5 like this 200,220.00
but doesn't work for me as in code above and bold lines
any help to fix it ,please ?

Try using the .AddItem method. It worked for me recently.

 

[abdelfattah]

I am also assuming that your object variable ws has been intialized elsewhere in your project.

yes that's correct
your new version works perfectly .
thank you so much , Dave .

 

[dmt32]

your new version works perfectly .
thank you so much , Dave .

Most welcome & appreciate your feedback

Dave