Run Routine Automatically

[sswilcox]

I've searched previous threads and have not found a solution.

Cell H40 contains the results of a calculation. If the value of H40 is
greater than 0.05 (5%), I want a message box to pop up to warn the
user. I have already written a piece of code to handle the conditions
of the message box and make it appear. No problems here.

The issue I cannot seem to work through is tying that Sub to the
Worksheet_Change event. I've tried most, if not all, of the
suggestions I've found here. I just don't see any response from my
workbook when the value in cell H40 changes. Is it because H40 is
calculated and I am not changing its value directly?

There are seven other user-entered inputs - all numeric - that
ultimately determine the number contained in H40.

I appreciate any help you can offer.

 

[Gord Dibben]

Use a calculate event rather than change event.

Private Sub Worksheet_Calculate()
your code here
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

More info...........

When in the worksheet module click in Procedure dialog(rightside top) to see
a list of event types.


Gord

 

[sswilcox]

Thanks for the quick response, Gord.

This still doesn't seem to be working. Here is my revised code:

Private Sub Worksheet_Calculate()
If Range("H40").Value > 0.05 Then
MsgBox "My message here"
Else
End If
End Sub

It works if I just run the code manually within VBE, but not when I
change something in the worksheet that causes H40 to change.

 

[Gord Dibben]

Have you stored the code in the worksheet module?

Is calculation mode set to Automatic?

Works for me as is but I would qualify the sheet.

If Me.Range("H40").Value > 0.05 Then


Gord

 

[Niek Otten]

The code should be in the Sheet Module
From the worksheet, right-click the worksheet tab, View code, paste the code
there
Add a msgbox for the else condition, so you can be sure the module is called
anyway

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


Thanks for the quick response, Gord.

This still doesn't seem to be working. Here is my revised code:

Private Sub Worksheet_Calculate()
If Range("H40").Value > 0.05 Then
MsgBox "My message here"
Else
End If
End Sub

It works if I just run the code manually within VBE, but not when I
change something in the worksheet that causes H40 to change.

 

[sswilcox]

Ah-ha. Your "more info" reply tipped me off to the problem. I was
working in the "ThisWorkbook" module. I transferred my code to the
Sheet module in question and all appears to be right with the
universe.

Thanks Gord. Your help was clear and precise - exactly what I needed.

 

[sswilcox]

Looks like I've got a delay of 10-15 minutes with the board. Your 2nd
post fixed my problem. Thanks again.

 

[sswilcox]

Thanks for chiming in, Niek. I like your idea of a second message box
tied to the Else condition. However, I think in this case I will elect
to not implement that change. The purpose of the first message box is
to warn my traders/salespeople when their profit margin is excessively
high, indicating that one or more of their variables are incorrect.
This should be an extremely rare occurrence, so I don't want to pester
the users with non-stop message boxes that they have to clear.
I'll save your idea for another project.