Run Macro Based on Cell Contents?

[sobershea]

I would like to display a message when sheet 2 is selected and the contents of H4 (a formula) in sheet 2 is greater than, or less than 0. The message would be a vbOKOnly. Is that possible?

[HansV]

Right-click the sheet tab of Sheet2.
Select View Code from the popup menu.
Enter or copy/paste the following code into the module window that appears:

Code: Select all

Private Sub Worksheet_Activate()
  If IsNumeric(Range("H4").Value) Then
    If Range("H4").Value > 0 Then
      MsgBox "H4 is positive!", vbOKOnly
    ElseIf Range("H4").Value < 0 Then
      MsgBox "H4 is negative!", vbOKOnly
    End If
  End If
End Sub

[sobershea]

Hans,
The code works except that it triggers the message box even if the value is zero. I think that is because H4 is the result of a formula. The display is zero but if I expand the decimals it really is a minute value. I tried to make sure all my formulas are rounded but I still get the message box.

[HansV]

You could modify the code as follows:

Code: Select all

Private Sub Worksheet_Activate()
  Const intDecimals = 8
  If IsNumeric(Range("H4").Value) Then
    If Round(Range("H4").Value, intDecimals) > 0 Then
      MsgBox "H4 is positive!", vbOKOnly
    ElseIf Round(Range("H4").Value, intDecimals) < 0 Then
      MsgBox "H4 is negative!", vbOKOnly
    End If
  End If
End Sub

The value of H4 will be rounded to intDecimals decimal places - in this example I used 8 decimal places, but you can easily change the value of intDecimals to suit your needs. A larger value of intDecimals makes the comparison stricter, a smaller value makes the comparison looser.

[sobershea]

That did it. Works like a charm!

Thanks!