Format specific columns in listbox

[Bandito1]

Hi all,

I got this code that fills my listbox;

Option Explicit
Dim Ary As Variant

Private Sub UserForm_Initialize()
   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
End Sub

In some column are dates (for example column B)
That column shows the date in the listbox as 14-3-2023.
Can i format that specific column to dd/mmm/yy ?

In some columns are percentages (columns J, M, P, S, V)
Those percentages are displayed as 0.874949494.
Can i format a specific column so it displays 87%?

Generic question; how can i format a specific column in my code?

 

[Anthony47]

A Listbox shows "text", so you have to convert the info according their meaning before loading the control. For example:

Private Sub UserForm_Initialize()
   Ary = Sheets("BMR data").Range("A3", Sheets("BMR data").Range("A" & Rows.Count).End(xlUp).Offset(, 22)).Value
'Format conversion:
   For I = 1 To UBound(Ary)
        Ary(I, 15) = Format(Ary(I, 15), "dd-mmm-yyyy")      'Date
        Ary(I, 22) = Format(Ary(I, 22), "0.0%")             '%
   Next I
   With Me.lstSearchResults
'etc
'etc

This way I converted a date in column 15 and a percentage in column 22