View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Two short questions

Thanks. Worked great. However I have two more questions in order to
tweak
the display.

1) the format I used ("$##0.00) causes the numbers to not line up. If
there is no money made on a job, then I get $0.00, when I need $ 0.00.
How
can I get the spaces in there? I tried "$000.00" also.


Well, you really can't do it properly because you are dealing with a
proportionally spaced font, and <space is not as wide as a digit.
Space's and
commas are about 1/2 the width of a digit, so you could right-justify the
MsgBox and then pad with twice the number of spaces as there are
characters in
the numeric string. This gets you close but not exact. Maybe close
enough?

2). I want to underline between 2nd line and Total. How do I get the
underline in there?


Again, you can't underline the font in a message box. About the best you
can
do is draw a line of the proper length. That will give you an "extra"
line,
though, rather than an underlined line.

Something like below will do what I've described:

=============================================
Option Explicit
Sub foo()
Const dVar1 As Double = 930
Const dVar2 As Double = 2
Const sFmt As String = "#,##0.00"
Dim res As String

Dim d1pad As String
Dim d2pad As String
Dim d3pad As String
Dim dL As Long 'used to compute padding

dL = Application.WorksheetFunction.Max( _
Len(Format(dVar1, sFmt)), _
Len(Format(dVar2, sFmt)), _
Len(Format(dVar1 + dVar2, sFmt))) + 3


With Application.WorksheetFunction
d1pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar1, sFmt))))
d2pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar2, sFmt))))
d3pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar1 + dVar2,
sFmt))))
End With

res = MsgBox("EMS Job 1:" & d1pad & Format(dVar1, sFmt) & _
vbLf & "EMS Job 2:" & d2pad & Format(dVar2, sFmt) & _
vbLf & Application.WorksheetFunction.Rept("_", Len(d3pad)) & _
vbLf & "TOTAL:" & d3pad & Format(dVar1 + dVar2, sFmt), vbMsgBoxRight)


End Sub
================================================


If you make dVar1 = 1234, the alignment of the text and $ sign doesn't match
any more. Because of the thousands separator perhaps?

Rick