Format columns in a listbox as Currency

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
ok i will check tomorrow for you
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
ready for a look
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

latest version?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
when do you want the approval window available?
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
100
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

when do you want the approval window available?
That's okay as it is (y)

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

1613893719424.png


This process has been a great education for me. THANKS
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
Been a pleasure. Hope it proves a useful tool. Even better if you picked up some tips :)
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
100
Office Version
  1. 365
Platform
  1. Windows
Been a pleasure. Hope it proves a useful tool. Even better if you picked up some tips :)
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:

VBA Code:
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
 
Last edited:

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,284
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top