Run Routine Automatically

  • Thread starter Thread starter sswilcox
  • Start date Start date
S

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.
 
Use a calculate event rather than change event.

Private Sub Worksheet_Calculate()
your code here
End Sub


Gord Dibben MS Excel MVP
 
More info...........

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


Gord
 
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.
 
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
 
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.
 
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.
 
Looks like I've got a delay of 10-15 minutes with the board. Your 2nd
post fixed my problem. Thanks again.
 
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.
 
Back
Top