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]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

integrated both i think. have not done filter options yet

Perfect... Everything is in there

 

[Lars1]

Perfect... Everything is in there

just a small correction / question...
How to change format to 09 : 05

AND...
When i click on "Save new record" the listbox shows all records
When i click on "Finalise record" the listbox shows all records.
How to only show records for the relevant employee ?

 

[diddi]

have to look at the record filter still. i can fix the show all records after click easily

formatting change

    With ws
        .Cells(lRow, 1).Value = cboID.Value
        .Cells(lRow, 2).Value = cboID.List(lPart, 1)
        .Cells(lRow, 4).Value = Format(txtDate.Value, "dd-mmm-yyyy")
        tempStart = Format(txtStart.Text, "00") & ":" & Format(txtStart2.Text, "00")
        .Cells(lRow, 5).Value = tempStart

 

[diddi]

Private Sub cboID_Change()
    If cboID.ListIndex = -1 Then
        Frame1.Visible = False
    Else
        Frame1.Visible = True
        FillContacts cboID.Text
    End If
    Frame2.Visible = False
End Sub

 

[Lars1]

Private Sub cboID_Change()
    If cboID.ListIndex = -1 Then
        Frame1.Visible = False
    Else
        Frame1.Visible = True
        FillContacts cboID.Text
    End If
    Frame2.Visible = False
End Sub

Then the ListBox is not updated unless i change to a new user and then go back

 

[diddi]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Lars1]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Lars1]

Nice solution
But it looks like the ListBox is not showing records before i change to another user and go back again...

I have changed the default OptionButton to 2.
OptionButton2.Value = True

 

[diddi]

what is the sequence of actions you are doing to make the problem

 

[Lars1]

what is the sequence of actions you are doing to make the problem

1. select employee

2. Click on "save new record

3. Nothing in ListBox or in Employee ID