Excel VBA formatting and variable output not always working

1370 views excel

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
End Sub

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

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

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. enter image description here

answered question

2 Answers


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 use:

Target.Offset(0, 3).Value = 0.7
Target.Offset(0, 3).NumberFormat = "currency"

posted this

Can you try to change this line:

Dim price As Double

Good Luck

posted this

Have an answer?


Please login first before posting an answer.