Excel VBA formatting and variable output not always working

Question

If a specific value will entered in row "A", a specific price should be inserted into row "D" and after that the entered price should be displayed in a messagebox.

The first part was just an easy setup, but with the msgbox I have actually some issues. Maybe because of the procedure of the code?! The price is just in this moment inside the cell and my code is already trying to get this in the moment empty cell?! - not sure.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler

Dim price As String

If Target.Column = 1 And Target.Value = "XY01" Then
    Application.EnableEvents = False
    Target.Offset(0, 3) = Format(0.7, "currency")
    Application.EnableEvents = True
    price = ActiveCell.Offset(0, 3).Value
    MsgBox "The price is now " & price
End If
Handler:
End Sub

The really strange thing is that inside the first row it will be displayed as excepted:

Just in every other row it will be displayed like this (it's just empty):

My 2nd question is that I have formatted the value as "currency", but I'm anyway get this error message (in English like that the cell is formatted as text). Also by formatting the cell by the excel tools the error message will not disappear.

Any Idea to fix this?

Thank you guys.

==============

EDIT

I have updated my code to following, so I was able to solve my 2nd question regarding the note that my value is just a text.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler

Dim price As String

If Target.Column = 1 And Target.Value = "XY01" Then
    Application.EnableEvents = False
    Target.Offset(0, 3).Value = 0.7
    Target.Offset(0, 3).NumberFormat = "currency"
    Application.EnableEvents = True
    price = Target.Offset(0, 3).Text
    MsgBox "The price is now " & price
End If
Handler:
End Sub

I don't know why, but now will not be any msgbox displayed?! Also the price will now only once inserted, if I type in the code again in another cell (a cell down) the code seems not be running again?!

I need to reopen excel to bring it up to work again.

To your edit: The issue is that your error handler is silent. If a error occurs it jumps to `Handler:` and nothing happens (no message at all). Therefore you cannot notice any errors. comment out `On Error GoTo Handler` to debug it and implement a proper error handling later: See [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) — Pᴇʜ, Nov 07 '18 at 13:26

Pᴇʜ · Accepted Answer · 2018-11-07T13:45:24.090

3

The Format function always returns a string/text.

So here: Target.Offset(0, 3) = Format(0.7, "currency") you don't write a numeric value but a text.

Instead write the value and set the number format of the cell:

Target.Offset(0, 3).Value = 0.7
Target.Offset(0, 3).NumberFormat = "#,##0.00 $"

Then you can read the cell's .Text (instead of .Value) to get it formatted as shown in cell:

Dim price As Sting
price = Target.Offset(0, 3).Text
MsgBox "The price is now " & price

or read the cell's Value and format it whatever you like:

Dim price As Double
price = Target.Offset(0, 3).Value
MsgBox "The price is now " & Format(price, "#,##0.00 $")

edited Nov 07 '18 at 13:45

answered Nov 07 '18 at 12:59

Pᴇʜ

56,719
10
49
73

Thanks, your answer makes absolutely sense to me. But it seems not be working now. I will update my original post with further details. – L.Writer Nov 07 '18 at 13:17
@L.Writer Ah didn't see it. You used `ActiveCell` instead of `Target` so it might read from another cell than you wrote in. Fixed my answer. – Pᴇʜ Nov 07 '18 at 13:19
I have used your update, but it seems still not to be working for me?! I have updated my original post with some additional details, hopefully this will help. – L.Writer Nov 07 '18 at 13:27
Comment out `On Error GoTo Handler` and tell which error you get and where please. – Pᴇʜ Nov 07 '18 at 13:29
Ah sorry, missed the error handler... ^^ `Target.Offset(0, 3).NumberFormat = "currency"` seems to be the issue.. I'm receiving **runtime error 1004** – L.Writer Nov 07 '18 at 13:37
1

Ah I missed that too. Obviously `"currency"` is no valid number format. So you must use something like `"#,##0.00 $"`. See [Range.NumberFormat Property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformat) and [Range.NumberFormatLocal Property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformatlocal) – Pᴇʜ Nov 07 '18 at 13:43

score -2 · Answer 2 · answered Nov 07 '18 at 12:59

-2

Can you try to change this line:

Dim price As Double

Good Luck

answered Nov 07 '18 at 12:59

adhy wijaya

509
3
7

Excel VBA formatting and variable output not always working

EDIT

2 Answers2