Message Box and/or Change Cell event

[delish]

I've got one here I can't figure out! What I'm trying to do is look at a cell and if when it changes it falls within a certain range I trigger a message box or an email. I know how to create a message box or an email using VBA but the trick is finding an event that I can use to evaluate the cell that I'm looking at. This sheet is linked to a live data source using a plug-in so cells are constantling being updated. I can write a loop that continues to look at the cell but that incapacitates the sheet. How can I evaluate this cell (or range of cells)??? There doesn't seem to be a "on cell change event." Any ideas??

 

[Legacy 98055]

Hi Delish.
I'm guessing that you are using DDE.
If not, the Worksheet_Change event would suffice. If you are using DDE, the only way I have managed the ability to track and test changes at the destination is by creating a link to another cell and tracking the changes in that cell. DDE will not fire the WorksheetChange_Event. You may also play around with the Worksheet_Calculate event as well. Depends on how many calculations will be occuring.

Example:
Cell A1 will be providing the DDE updates.
Throw this formula in A2.

=A1

Now, in the Worksheet_Change procedure, test for your conditions...
<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A2").Value
Case 1: MsgBox "DDE placed 1"
Case 2: MsgBox "DDE placed 2"
Case 3: MsgBox "DDE placed 3"
'ect....
End Select
End Sub

</pre>

I have to go but will check back to this thread later to see what progress you have made.

Tom

 

[stevebausch]

TsTom is dead-on about Worksheet_Change. I rely upon it for a variety of validations on my project.

Now, if you find it giving you a Type Mismatch run-time error, that is so easy to avoid.

What that error is about is merged cells, and multiple selections. Your version of Excel will affect this issue. Excel 97 is pretty clumsy about the Type of Target returned.

If you have any problems, the first step to trouble-shooting would be to change any Target references in your code to Target(1,1) which indexes into the Range returned by target.

And, when you get adventureous, a For Each..Next loop will allow your validation program to check every single cell in a multiple selection.

 

[delish]

This was my first time on a message board so I don't know proper etiquette at this point but I wanted to thank both of you that responded. The combination of your answers helped me solve my problem better than I ever imagined it could be solved.

THANK YOU!!