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]

ah yes i cleared the list because i assumed an employee would enter and then finish. if they stay and do several entries then it needs to be different

 

[Lars1]

ah yes i cleared the list because i assumed an employee would enter and then finish. if they stay and do several entries then it needs to be different

They need to see what they have entered. Otherwise the most people assume that there was an error and they will try again.
So to see the item in the ListBox is a confirmation on what they have entered.

 

[diddi]

ok i will check tomorrow for you

 

[diddi]

ready for a look

 

[diddi]

latest version?

 

[diddi]

when do you want the approval window available?

 

[Lars1]

when do you want the approval window available?

That's okay as it is

I Think we have more or less reached the goal here

The only thing i can see is missing, is the listBox that shows the records as mentioned before and the formatting of the time and minutes. they should be 08:05 instead of 8:5

This process has been a great education for me. THANKS

 

[diddi]

Been a pleasure. Hope it proves a useful tool. Even better if you picked up some tips

mReXCEL_Final.xlsm

Shared with Dropbox

www.dropbox.com

 

[Lars1]

Been a pleasure. Hope it proves a useful tool. Even better if you picked up some tips

mReXCEL_Final.xlsm

Shared with Dropbox

www.dropbox.com

Hello Diddi
Hope everything is well and you are enjoying life

There is one thing i am struggling with here. And i hope you could help.
It's quit basic, but i have not been able to find a solution.

The solution we have created is rather important to me, and i would like to create a kind of back-up.
Therefore i would like to copy a sheet called "database" to another existing excel workbook.

I have tried this one:

Private Sub commandButtonCopy_Click()
    CopyWorkbook
End Sub

Sub CopyWorkbook()

Workbooks("LP_TEST_Akkord_SCANNER - Kopi.xlsm").Worksheets("Database").Copy

End Sub

And this works fine when i click the commandButton. It copies the right sheet, but to a new workbook
How can i copy to an existing workbook like for an example: F:\Accord\LP_TEST\2021.xlsx ?

AND, is it possible to schedule this on specific time/hours ?
Otherwise i probably will run this everytime i close the application...

Best regards
Lars

 

[diddi]

send me the most up to date copy. i will insert a transparent backup. glad it has turned out to be a useful project for you