Accounting format

I have a message box that puts out 2 lines of currency values (along with text).  

Is there any way to format the currency so it aligns by decimal? (Accountant format)
thandelAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
That won't work because the MsgBox doesn't normally use a monospaced font; in other words each number or letter may be of different widths. It might be possible to subclass the msgbox so that it does use a monospaced font but even if that allowed you to align the decimals with something like this (which doesn't work because of the font problem) it would be ugly because monospaced fonts are IMO ugly.

    Dim Amt1 As String
    Dim Amt2 As String
    Dim FormatString1 As String
    Dim FormatString2 As String
    
    Amt1 = 20000.19
    Amt2 = 5125.12
    
    FormatString1 = "0123456789" ' Initialize string.
    FormatString2 = "0123456789" ' Initialize string.
    
    RSet FormatString1 = Amt1
    RSet FormatString2 = Amt2
    
    Debug.Print FormatString1
    Debug.Print FormatString2
    
    MsgBox FormatString1 & vbCrLf & FormatString2

Open in new window


I think the only choice you have is to accept the ugliness and use a UserForm that contains a label along with this code

Private Sub UserForm_Initialize()
    Dim Amt1 As String
    Dim Amt2 As String
    Dim FormatString1 As String
    Dim FormatString2 As String
    
    Amt1 = 20000.19
    Amt2 = 5125.12
    
    FormatString1 = "0123456789" ' Initialize string.
    FormatString2 = "0123456789" ' Initialize string.
    
    RSet FormatString1 = Amt1
    RSet FormatString2 = Amt2
    
    Debug.Print FormatString1
    Debug.Print FormatString2
    
    Label1.Font = "courier"
    Label1.Caption = "This is ugly but it aligns" & vbCrLf & vbCrLf & FormatString1 & vbCrLf & FormatString2

End Sub

Open in new window


If you do you'll get this:
Ugly but aligned
0
 
thandelAuthor Commented:
Thanks for the clear code and example.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.