Format columns in a listbox as Currency

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
109
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
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
109
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub SortDateColumnDown(myArray As Variant, ColNum As Integer)

' myArray is the 2d array to be sorted
' ColNum is the sort column (base 0)
' Revision 2: updated to support dates after 1900 ONLY

    Dim Tempi As String, Tempj As String, I As Long, J As Long
    Dim NumCols As Long, Col As Long, sTemp As String
    Dim NewArray
    If UBound(myArray, 1) = 0 Then Exit Sub  ' <------   add this new line
Works if there is a line in the listbox on that specific employee.
If i select an Employee which is empty, then the error comes again.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
If UBound(myArray, 1) < 2
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
109
Office Version
  1. 365
Platform
  1. Windows
PERFECT. Once again thanks for your help Diddi (y)
Oh oh...
missed one more error.

The backup file is apparantly not an excel file...
When i try to open it:
1615193290208.png

The file can not open, because the fileformat is invalid. Control that filename equals filetype format.


VBA Code:
Sub CopyWorkbook()
    MsgBox "Backup er i gang. Klik på >OK< og vent..."
   
    With Sheets("Database")
        Dim A, Row As Long, Col As Long, LastRow As Long
       
        Open "F:\Accord\LP_TEST\AkkordData.xlsx" For Output As #1
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For Row = 1 To LastRow
                For Col = 1 To 23
                    Print #1, .Cells(Row, Col) & Chr(9);
                Next Col
                Print #1, ""
            Next Row
        Close
    End With
   
    StartTimer
End Sub
 

diddi

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

ADVERTISEMENT

haha have you seen how many views this has? over 800 LOL
you will be able to sell autographs
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
oh yeh i dumped the data as a tab delimited text file. if you open it in notepad, you can just copy / paste it into excel again. it is afterall an emergency backup, so not likely to be used that often.
 

Lars1

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

ADVERTISEMENT

oh yeh i dumped the data as a tab delimited text file. if you open it in notepad, you can just copy / paste it into excel again. it is afterall an emergency backup, so not likely to be used that often.
It will actually be used quit often...
The back-up file be the source file for other applications to get data from...

And it´s you who should do the autographs. you are the MASTER
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
haha. well you can have an autograph for free then :)

you should find the backup will be small and easy to paste in. if it is no goo like that i can have a play with it.
now with the listbox deletion, is that deleting from the table as well, and will there be multiple lines to delete
i can think about it for tomorrow.
 

Lars1

Board Regular
Joined
Feb 3, 2021
Messages
109
Office Version
  1. 365
Platform
  1. Windows
haha. well you can have an autograph for free then :)

you should find the backup will be small and easy to paste in. if it is no goo like that i can have a play with it.
now with the listbox deletion, is that deleting from the table as well, and will there be multiple lines to delete
i can think about it for tomorrow.
Looking forward to the autograph (y) :biggrin: 🍷

I think i need the back-up file to be in an excel format.


And regarding the deletion, it will only be one line at the time.
- find an employee
- click the line we would like to delete
- click delete
- confirm deletion
All this ONLY when you are logged in as an administrator and from the "administrator" frame
Please notice it is a "nice to have" function, and not a "need to have" function
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
ok. thats not too hard to do. its on for next time i am free
 

Forum statistics

Threads
1,136,274
Messages
5,674,774
Members
419,525
Latest member
helensesc

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