Launch a MSG Box when cell value changes but is not typed in

[Tanner_2004]

I want to launch a message box whenever a specific cell (h12) is equal to 1 (its other value is 0). However, this cell value is not typed in; it is enter using an IF statement (so using a formula). Any way to account for the change when the cell is value is not typed in or fool the macro into thinking that the cell value is being typed in? Thank you.

 

[GTO]

I chose F1 for the formula cell on a default named Sheet1.

Excel Workbook
	B	C	D	E	F
1	1	2	3		1
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F1	=IF(SUM(B1:D1)<7,1,0)

Hopefully I am not forgetting something, but basically I think you'll want to keep the return value of the formula stored, so that you don't get the msgbox each time anything calculates. So...

In a Standard Module:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Public</SPAN> StartVal<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN></FONT>

In the Worksheet's Module:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> Cells(1, "F").Value = StartVal<SPAN style="color:#00007F">Then</SPAN><br>        StartVal = Cells(1, "F").Value<br>        <SPAN style="color:#00007F">If</SPAN> Cells(1, "F").Value = 1<SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Dude!  I equal 1 "<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Does that help?

Mark

 

[GTO]

ACK! I forgot...

In the ThisWorkbook Module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    StartVal = Sheet1.Cells(1, "F").Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Tanner_2004]

ACK! I forgot...

In the ThisWorkbook Module:
Option Explicit
****
Private Sub Workbook_Open()
****StartVal = Sheet1.Cells(1, "F").Value
End Sub

It worked BUT the message box doesn't close unless I hit the OK or Exit box about 10 times rapidly. Thank you though.

This is what my code looks like:
Option Explicit
Private Sub Worksheet_Calculate()
If Not Cells(12, "h").Value = StartVal Then
StartVal = Cells(12, "h").Value
If Cells(12, "h").Value = 1 Then
MsgBox "Dude! I equal 1 "
End If
End If
End Sub