Automatic message when a condition is met.

[kangaris]

Hello,

I am trying to insert a macro that will pop up a message when a condition is met. I use the following code:

Sub Worksheet_Change(ByVal Target As Range)
    If Range("B4").Value < Range("C4").Value Then
        MsgBox ''Limit has been reached''
    End If
End Sub

However, it doesn't seem to work. It actually cannot be saved in my macros.

Can someone help?

Regards

Andreas

 

[Joe4]

How are the values in B4 and C4 being updated?
What is the name of the module that you have placed this VBA code in?

Not sure what you mean by this statement:

It actually cannot be saved in my macros.

Can you clarify?

 

[popipipo]

You are using the wrong quotes

Sub Worksheet_Change(ByVal Target As Range)
    If Range("B4").Value < Range("C4").Value Then
        MsgBox "Limit has been reached"
    End If
End Sub

 

[kangaris]

How are the values in B4 and C4 being updated?
What is the name of the module that you have placed this VBA code in?

Not sure what you mean by this statement:

Can you clarify?

So this is a screenshot of my VBA.
B4 and C4 was just a test I was trying in another excel sheet.

For example when I press run, the window with the rest of the macros pop up and the new module is not present there.

https://imgur.com/a/9o9OC2v

 

[kangaris]

https://imgur.com/a/9o9OC2v

For example when I press run the following window with the rest of the macros pop up but the new macro does not exist.

 

[Joe4]

I cannot view your images. Those sites are blocked by my workplace security.

You are using event procedure VBA code, which is code that automatically runs upon some event happening (in this case, the manual update of data on your sheet). Since the code runs automatically, it will not show up in "Run Macros" box.

Did you see popipipo's comment about the quotes you are using in your Message Box? You will need to include those.

If you still cannot get it to work, please answer the first two questions in my initial reply:

How are the values in B4 and C4 being updated?
What is the name of the module that you have placed this VBA code in?

"How are the values in B4 and C4 being updated?
What is the name of the module that you have placed this VBA code in?

 

[Fluff]

The code is in the wrong place, it needs to go in a sheet module not a regular module.
Also the code in your image is completely different from the code you have posted here!
Which code do you want to use & which sheet should it be working on?

 

[kangaris]

Ok the first code was just a trial I did to make the macro work.
The original code is this

Sub Worksheet_Change(ByVal Target As Range)    
If Worksheets(SizingHX).Range("Q50").Value < 100 Then
        MsgBox "Limit has been reached"
    End If
End Sub

Joe4, what do you mean by the name of the module?

 

[Fluff]

Then try

Sub Worksheet_Change(ByVal Target As Range)
If Range("Q50").Value < 100 Then
        MsgBox "Limit has been reached"
    End If
End Sub

Right click the tab for sheet SizingHX & select view code. Then paste the code into the window that opens up

 

[kangaris]

Thanks a lot! It works now! Now i have understood the difference between sheet and regular module.

I have a further query. The message pops up only when I am on the current sheet that the code was placed. I have a summary sheet where I input data and once I put data that will meet the condition of my code, the message does not pop up.