Format columns in a listbox as Currency

[Lars1]

Hi
I am quite new to VBA, and i have a problem with som formats in a listbox.
I would like column 8 and column 10 formatted as currency #.###,## kr the Danish format.

As it is now it comes out with plain numbers like 1000 and the result i am looking for is 1.000,00 kr

There are also two columns formatted as "Short Time", but this is maybe not the best way to format these two columns



Private Sub FillContacts(Optional sFilter As String = "*")
Dim i As Long, j As Long

'Clear any existing entries in the ListBox
Me.ListBox1.Clear

'Loop through all the rows and columns of the contact list
For i = LBound(maContacts, 1) To UBound(maContacts, 1)
For j = 1 To 10
'Compare the contact to the filter
If UCase(maContacts(i, j)) Like UCase("*" & sFilter & "*") Then
'Add it to the ListBox
With Me.ListBox1
.AddItem maContacts(i, 1)
.List(.ListCount - 1, 1) = maContacts(i, 2)
.List(.ListCount - 1, 2) = maContacts(i, 3)
.List(.ListCount - 1, 3) = maContacts(i, 4)
.List(.ListCount - 1, 4) = maContacts(i, 5)
.List(.ListCount - 1, 4) = Format(Time, "Short Time")
.List(.ListCount - 1, 5) = maContacts(i, 6)
.List(.ListCount - 1, 5) = Format(Time, "Short Time")
.List(.ListCount - 1, 6) = maContacts(i, 7)
.List(.ListCount - 1, 7) = maContacts(i, 8)
.List(.ListCount - 1, 8) = maContacts(i, 9)
.List(.ListCount - 1, 9) = maContacts(i, 10)
End With
'If any column matched, skip the rest of the columns
'and move to the next contact
Exit For
End If
Next j
Next i
'Select the first contact
If Me.ListBox1.ListCount > 0 Then Me.ListBox1.ListIndex = 0

 

[diddi]

in an hour or so...

 

[diddi]

let me know when you are free

 

[Lars1]

let me know when you are free

Okay. I am here now

 

[diddi]

vg

 

[diddi]

lets try the sort first if you do a sort on what you see, you can no longer rely on the listindex to determine what has been selected so we will have to create an index reference stored in the list that re-orders if sorted. this is easy... just add one more line inserting the row counter (you used i ) into the listbox list

                    With ListBox1
                        .AddItem maContacts(i, 1)
                        .List(.ListCount - 1, 1) = maContacts(i, 2)
                        .List(.ListCount - 1, 2) = maContacts(i, 3)
                        .List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy")
                        .List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm")
                        .List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm")
                        .List(.ListCount - 1, 6) = maContacts(i, 7)
                        .List(.ListCount - 1, 7) = maContacts(i, 8)
                         If maContacts(i, 8) <> "" Then .List(.ListCount - 1, 7) = FormatCurrency(maContacts(i, 8))
                        .List(.ListCount - 1, 8) = maContacts(i, 9)
                        .List(.ListCount - 1, 9) = maContacts(i, 10)
                        If maContacts(i, 10) <> "" Then .List(.ListCount - 1, 9) = FormatCurrency(maContacts(i, 10))
                        .List(.ListCount - 1, 11) = i   '   add index here
                    End With

 

[diddi]

and you need a sort routine. i have a standalone one in my snippits library. so here it is

Sub SortDateColumnDown(myArray As Variant, ColNum As Integer)

' myArray is the 2d array to be sorted
' ColNum is the sort column (base 0)

    Dim tempi As String, tempj As String, i As Long, J As Long
    NumCols = UBound(myArray, 2)

    For i = 0 To UBound(myArray, 1) - 1
        For J = i + 1 To UBound(myArray, 1)
            tempi = DateValue(myArray(i, ColNum))
            tempj = DateValue(myArray(J, ColNum))
            If tempi < tempj Then
                For col = 0 To NumCols
                    sTemp = myArray(i, col)
                    myArray(i, col) = myArray(J, col)
                    myArray(J, col) = sTemp
                Next
            End If
        Next J
    Next i
End Sub

you can put this in your userform code, or in a module. i would also add it to your collection of useful snippits so you can reuse it again

 

[Lars1]

lets try the sort first if you do a sort on what you see, you can no longer rely on the listindex to determine what has been selected so we will have to create an index reference stored in the list that re-orders if sorted. this is easy... just add one more line inserting the row counter (you used i ) into the listbox list

                    With ListBox1
                        .AddItem maContacts(i, 1)
                        .List(.ListCount - 1, 1) = maContacts(i, 2)
                        .List(.ListCount - 1, 2) = maContacts(i, 3)
                        .List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy")
                        .List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm")
                        .List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm")
                        .List(.ListCount - 1, 6) = maContacts(i, 7)
                        .List(.ListCount - 1, 7) = maContacts(i, 8)
                         If maContacts(i, 8) <> "" Then .List(.ListCount - 1, 7) = FormatCurrency(maContacts(i, 8))
                        .List(.ListCount - 1, 8) = maContacts(i, 9)
                        .List(.ListCount - 1, 9) = maContacts(i, 10)
                        If maContacts(i, 10) <> "" Then .List(.ListCount - 1, 9) = FormatCurrency(maContacts(i, 10))
                        .List(.ListCount - 1, 11) = i   '   add index here
                    End With

I don´t think it´s the right file / Version...

The one i put on dropbox is not the one you are working in...

 

[diddi]

send it again pls
i am busy for about 45 mins... you should still be able to insert that new sub and add the new line in the listbox1 populating part

 

[Lars1]

send it again pls
i am busy for about 45 mins... you should still be able to insert that new sub and add the new line in the listbox1 populating part

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

When i use you last code, i get this error:

 

[diddi]

it seem the database object is linked to the listbox.
you will need to add a new column in the database and then we can access the index,