Search code examples
excelvbaformattingmsgbox

Excel VBA formatting and variable output not always working

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

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

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.

Solution

  • 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 $")