Message Box and/or Change Cell event

delish

New Member
Joined
Oct 10, 2002
Messages
8
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??
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
L

Legacy 98055

Guest
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

New Member
Joined
Oct 10, 2002
Messages
8
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top