Changing Number format in VBA message Box

[jumbo99]

I have a message box in VBA which displays the variable "cash". I am using the "currency" format as below:

cash = Format(cash, "currency")

The problem is that if the amount goes over £10000 it does not put a comma after the first zero (£10,000) to make it clearer to read. I have tried every combination of #,0 etc to make a custom format but it still does not
show a comma after the first zero.

Any ideas.

 

[Fluff]

Hi & welcome to MrExcel.
Is the variable "cash" declared, if so as what?

 

[jumbo99]

Hi & welcome to MrExcel.
Is the variable "cash" declared, if so as what?

no its not a declared var.

 

[Gokhan Aycan]

Either of the following is working for me:

msgbox format("10000","currency")
msgbox format(10000,"currency")

I tested also in a sub (Cash as Variant, Currency, String), assigning the value 10000 and/or "10000".

Can you post more of the code please?

 

[jumbo99]

no its not a declared var.

I have now tried it by declaring it: dim cash as currency but it does not make a difference.

 

[Fluff]

If it's not declared it should work, otherwise you would need to declare it as variant, or just format the variable when you display the msgbox.

 

[jumbo99]

I have just tried creating a new sheet and it works, giving me a comma after the first zero. So it must be something with my very large first sheet. Any ideas. Thanks.

 

[Fluff]

Any ideas. Thanks.

Not really without knowing how you assigned a value to it. Although one possibility is that you are assigning a text value, rather than a number.

 

[jumbo99]

Here is the code when I was trying with the #,0 etc:

For Each cell In Range("B1:B143")

If (cell.Value = "CGAR") Or (cell.Value = "CGT") Or (cell.Value = "PNL") Or (cell.Value = "VLS20") Or (cell.Value = "VLS40") Then safetyfunds = safetyfunds + cell.Offset(0, 5).Value
If (cell.Value = "CASH") Then cash = cash + cell.Offset(0, 5).Value
Next cell

' cash = Format(cash, "Currency")
MsgBox "Safety Funds (CGT, CGAR, VLS40 and VLS 20 : " & Format(safetyfunds, "£#,000.00" & vbNewLine & vbNewLine & "Cash: " & Format(cash, "£#,000.00"))

 

[Fluff]

You have a bracket in the wrong place, it should be

MsgBox "Safety Funds (CGT, CGAR, VLS40 and VLS 20 : " & Format(safetyfunds, "£#,000.00") & vbNewLine & vbNewLine & "Cash: " & Format(cash, "£#,000.00")