CODEWITHSUNDEEP
excelformatnumber-formattingvba
John Joseph
John Joseph

Reputation: 1175

Using Excel 2007+ VBA and a Cell's NumberFormat Property to Format Number Text

I would like to use VBA and a cell's format when displaying related numbers with MsgBox and/or in concatenated text strings.

I have tried using the cell's NumberFormat property but get unusable results...

e.g. the following sample code should tell a user to charge $4.50 for lemonade that is marked up 150% above cost...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "I should charge " & Format(Range("a3").Value * 1.5, Range("a3").NumberFormat) & " for this lemonade."
End Sub

...results in...

Sample output when the source cell is unformatted and formatted as currency...

There are two scenarios displayed: the first is an unformatted cell; the second is a currency-formatted cell. I would like my code to accommodate any valid number format, including custom formats.

Upvotes: 0

Views: 311

Answers (1)

Rory
Rory

Reputation: 34045

As long as you use US regional settings, you can use Application.Text instead of Format:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "I should charge " & Application.Text(Range("a3").Value * 1.5, Range("a3").NumberFormat) & " for this lemonade."
End Sub

for example. If you don't have US settings, you'll still need to work around any localised currency formats.

Upvotes: 1

Related Questions