number formatt"#,##0.00" doesn't work in two columns in listbox on form

[Mussala]

Hello,
I try to show amounts in column 4,5 in list box on form like this ("#,##0.00) but doesn't work as in bold lines!!

Sub LoadData()

    Dim s$(1), x, e
    For Each e In Array("Mussala", "mssau", "mjhgsg")
        s(0) = s(0) & IIf(s(0) = "", "", "Union All (") & "Select " & _
        "Format(`DATE`,'dd/mm/yyyy'), `INVOICE NO`, `TYPE`, `DEBIT`, `CREDIT`, " & _
        "`BALANCE` From `" & e & "$` Where `TYPE` <> 'OPENING' And `TYPE` Is Not Null " & IIf(s(0) = "", "", ") ")
    Next
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        x = .GetRows
    End With
    With Me.ListBox1
        .ColumnCount = UBound(x, 1) + 1
        .Column = x
        For i = 0 To ListCount - 1
    .List(i, 3) = Format(.List(i, 3), "#,##0.00")
     .List(i, 4) = Format(.List(i, 4), "#,##0.00")
     Next i
    End With
   
End Sub

what's the problem guys?!

 

[dmt32]

Hello,
I try to show amounts in column 4,5 in list box on form like this ("#,##0.00) but doesn't work as in bold lines!!
what's the problem guys?!

Try including the period (full stop) in front of ListCount

       For i = 0 To .ListCount - 1
            .List(i, 3) = Format(.List(i, 3), "#,##0.00")
            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
        Next i

Dave

 

[Mussala]

Oops!
good catching .
but strangely doesn't show me error like syntax and still doesn't work after add period !

 

[Alex Blakenburg]

Try this additional change:

       For i = 0 To .ListCount - 1
            .List(i, 3) = Format(CCur(.List(i, 3)), "#,##0.00")
            .List(i, 4) = Format(CCur(.List(i, 4)), "#,##0.00")
        Next i

 

[Mussala]

sorry Dave this is my bad !
it works , many thanks.

 

[dmt32]

sorry Dave this is my bad !
it works , many thanks.

welcome glad we were able to resolve

Dave

 

[Mussala]

thanks Alex
your way shows me error invalid used o null in this line

.List(i, 3) = Format(CCur(.List(i, 3)), "#,##0.00")

any way my problem is fixed.

 

[Alex Blakenburg]

Thanks for letting me know. Glad Dave's solution worked for you.