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

 

[Lars1]

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,
View attachment 32212

I have done that, but still the same error.
I think my head is exploding here and i am going blind to the solution.

How should this line look like ?
.List(.ListCount - 1, 10) = i
Or
.List(.ListCount - 1, 10) = maContacts(i, 16) 'add index here



With ListBox1
.AddItem maContacts(i, 1)
.List(.ListCount - 1, 1) = maContacts(i, 2) 'Navn
.List(.ListCount - 1, 2) = maContacts(i, 3) ' godkendt af
.List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy") 'Dato
.List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm") ' start
.List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm") ' ****
.List(.ListCount - 1, 6) = maContacts(i, 7) 'Antal
.List(.ListCount - 1, 7) = maContacts(i, 8) 'Vare
.List(.ListCount - 1, 8) = maContacts(i, 9) 'Løn
If maContacts(i, 9) <> "" Then .List(.ListCount - 1, 8) = FormatCurrency(maContacts(i, 9))
.List(.ListCount - 1, 9) = maContacts(i, 10) ' Timeløn/akkord
.List(.ListCount - 1, 10) = i 'add index here
End With

 

[diddi]

yep. i had the same issue... then i remembered a "bug?" "issue" in excel, so i have a workaround.
do this first and then i will explain

                    With ListBox1
                        .AddItem maContacts(i, 1)
                        .List(.ListCount - 1, 1) = maContacts(i, 2) 'Navn
                        .List(.ListCount - 1, 2) = maContacts(i, 3) ' godkendt af
                        .List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy") 'Dato
                        .List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm") & "   to   " & Format(maContacts(i, 6), "hh:mm") ' start
                        '.List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm") ' ****
                        .List(.ListCount - 1, 6) = maContacts(i, 7) 'Antal
                        .List(.ListCount - 1, 7) = maContacts(i, 8) 'Vare
                        .List(.ListCount - 1, 8) = maContacts(i, 9) 'Løn
                         If maContacts(i, 9) <> "" Then .List(.ListCount - 1, 8) = FormatCurrency(maContacts(i, 9))
                        .List(.ListCount - 1, 9) = maContacts(i, 10) ' Timeløn/akkord
                        .List(.ListCount - 1, 5) = maContacts(i, 16) 'Index
                    End With

and change the properties if the listbox on the userform like this

31.95 pt;120 pt;120 pt;70.9 pt;80 pt;0 pt;40 pt;160 pt;60 pt;35 pt;0 pt

 

[Lars1]

yep. i had the same issue... then i remembered a "bug?" "issue" in excel, so i have a workaround.
do this first and then i will explain

                    With ListBox1
                        .AddItem maContacts(i, 1)
                        .List(.ListCount - 1, 1) = maContacts(i, 2) 'Navn
                        .List(.ListCount - 1, 2) = maContacts(i, 3) ' godkendt af
                        .List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy") 'Dato
                        .List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm") & "   to   " & Format(maContacts(i, 6), "hh:mm") ' start
                        '.List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm") ' ****
                        .List(.ListCount - 1, 6) = maContacts(i, 7) 'Antal
                        .List(.ListCount - 1, 7) = maContacts(i, 8) 'Vare
                        .List(.ListCount - 1, 8) = maContacts(i, 9) 'Løn
                         If maContacts(i, 9) <> "" Then .List(.ListCount - 1, 8) = FormatCurrency(maContacts(i, 9))
                        .List(.ListCount - 1, 9) = maContacts(i, 10) ' Timeløn/akkord
                        .List(.ListCount - 1, 5) = maContacts(i, 16) 'Index
                    End With

and change the properties if the listbox on the userform like this
View attachment 32213

31.95 pt;120 pt;120 pt;70.9 pt;80 pt;0 pt;40 pt;160 pt;60 pt;35 pt;0 pt

Still got the error...
And it looke like it´s the wrong file you are working on


With ListBox1
.AddItem maContacts(i, 1)
.List(.ListCount - 1, 1) = maContacts(i, 2) 'Navn
.List(.ListCount - 1, 2) = maContacts(i, 3) ' godkendt af
.List(.ListCount - 1, 3) = Format(maContacts(i, 4), "dd-mm-yyyy") 'Dato
.List(.ListCount - 1, 4) = Format(maContacts(i, 5), "hh:mm") ' start
.List(.ListCount - 1, 5) = Format(maContacts(i, 6), "hh:mm") ' ****
.List(.ListCount - 1, 6) = maContacts(i, 7) 'Antal
.List(.ListCount - 1, 7) = maContacts(i, 8) 'Vare
.List(.ListCount - 1, 8) = maContacts(i, 9) 'Løn
If maContacts(i, 9) <> "" Then .List(.ListCount - 1, 8) = FormatCurrency(maContacts(i, 9))
.List(.ListCount - 1, 9) = maContacts(i, 10) ' Timeløn/akkord
.List(.ListCount - 1, 10) = maContacts(i, 16) 'add index here
End With

 

[diddi]

its the one you just sent with the nice pic in the userform window. it is working for me.
you havent put in the new code and changed the properties

 

[Lars1]

its the one you just sent with the nice pic in the userform window. it is working for me.
you havent put in the new code and changed the properties

Sorry. My mistake...
Works fine now.

 

[diddi]

have you put that sort sub in as well

 

[Lars1]

have you put that sort sub in as well

No. not yet...

Nothing is writing in the index column...
Is this correct ?

 

[diddi]

oh good point. might help to write the index field. so find the sub that does the write and see what you can think of, i will take a look

 

[diddi]

any thoughts?

 

[Lars1]

any thoughts?

To many thoughts

I think something like this:

With ws
.Cells(lRow, 1).Value = cboID.Value
.Cells(lRow, 2).Value = cboID.List(lPart, 1)
.Cells(lRow, 4).Value = txtDate.Value
.Cells(lRow, 5).Value = txtStart.Value
.Cells(lRow, 6).Value = txtStop.Value
.Cells(lRow, 7).Value = txtQty.Value
.Cells(lRow, 11).Value = txtProduct.Value
.Cells(lRow, 12).Value = txtAccord.Value
.Cells(lRow, 16).Value = .Index = 1 + 1
End With

Except that i don´t know what the code is after:
.Cells(lRow, 16).Value =