Format currency in MsgBox

[Paul Breslin]

Hello -

Windows XP
Excel 2003

What I am trying to do is format a string for output in a message box for
currency.
Looking at the code below, when the application opens, the COSTS worksheet
is selected.
Variables are selected from the worksheet and displayed in a message box.
On the worksheet the cells in question are formatted for currency.
The message box opens fine and displays but not in the format I want.
The variables should be currency; that is, $1,234,567.00
What is being displayed is 1234567 - no dollar sign, no commas, no cents.

Is there some way to do this?

Thanks,
Paul
-------------
Private Sub Workbook_Open()

Application.WindowState = xlMaximized

myVar1 = Worksheets("COSTS").Range("C23")
myVar2 = Worksheets("COSTS").Range("C56")
myVar3 = Worksheets("COSTS").Range("C138")

'Open a Message Box

Dim Msg, Style, Title, Response

Msg = "Total Value of Category A = " & myVar1 & vbCrLf & vbCrLf & " Total
Value of Category B = " & myVar2 & vbCrLf & vbCrLf & " Total Value of
Category C = " & myVar3

Style = vbOKOnly + vbInformation ' Define buttons.
Title = " COSTS of REPAIRS" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbOKOnly Then

End If

End Sub

 

[Dave Peterson]

Format the variable (all of them??) the way you want:

myVar1 = format(Worksheets("COSTS").Range("C23"), "$#,##0.00")

 

[Bob Phillips]

Use the Text property, like so

myVar1 = Worksheets("COSTS").Range("C23").Text

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul Breslin]

Thank you to the responders. Paul