Displaying Messages When Automatic Data Changes

by Allen Wyatt
(last updated September 7, 2013)

1

Joydip has an Excel worksheet that is constantly and automatically updated with live commodity market data. He wants to display a message box containing a particular message whenever the data in a specified cell/range changes to meet some predefined criteria. Data validation won't work, because the validation feature isn't triggered when data changes automatically.

The best way to check the data and display the desired message box is to use a macro that is triggered by the Worksheet_Change event. This event is triggered any time the contents of a cell are changed. It is not, however, triggered by a change in what is displayed in a cell. For instance, if a new piece of commodity data is placed into a cell, then the event is triggered. However, if a formula is recalculated and a new result of that formula displayed, the event is not triggered. Why? Because the formula itself didn't change; it was only the result of the formula (what is displayed) that was changed.

Once the Worksheet_Change event is triggered, the macro can do anything you want it to do, including displaying your message. For this example, let's assume that the range to check is A1:C5 (this is where the commodity data is being inserted) and that the criteria you want to trigger the message is that the average of the range is 5. If the contents of any cell in the range is changed and the average of the values in the range is 5, then a message is displayed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("A1:C5")
    If Not Intersect(Target, rng) Is Nothing Then
        If Application.WorksheetFunction. _
          Average(rng) = 5 Then
            MsgBox "The average of " & _
              rng.Address & " = 5"
        End If
    End If
    Set rng = Nothing
End Sub

It is important that this macro be placed in the sheet object for the worksheet you want to monitor. When you display the VBA Editor, right-click on the desired worksheet in the Project Explorer area, then choose View Code from the resulting Context menu. This code window is where you place the macro.

The macro, again, is triggered anytime there is a change anywhere on the worksheet. The macro then uses the Intersect function to determine if the change occurred within the desired A1:C5 range. If it did, then the average of the range is checked, and the message displayed if the result is 5.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2906) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Chapter Numbers with Page Numbers

Do you need to add page numbers that include, as well, a chapter number? It's relatively easy to do, as described in this ...

Discover More

Segregating Numbers According to Their Sign

Remember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others above ...

Discover More

Extracting First and Last Words

When working with text phrases stored in cells, it might be helpful to be able to extract words from the phrase. In this tip ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Disabled Macros

Do your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's where ...

Discover More

Counting Commas in a Selection

If you have a range of cells in which you want to count all the commas, there are several ways you can derive the figure you ...

Discover More

Understanding the While...Wend Structure

Logical structures are important in programming, as they allow you to control how the programming statements are executed. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 1?

2015-12-26 21:15:16

Lloyd

I have been trying to have a message box displayed if the value in a cell is changed by the user. If the cell is empty then allow input , if the cell contains a value show a message box asking if they want to change the value "Yes or No" . Yhe following code is what I have worked with so far it almost works. Any help would be appreciated.
Public original As Variant
Private Sub Worksheet_Change(ByVal Target As Range)


Dim LResponse As Integer
If original = "" Then Exit Sub


LResponse = MsgBox("Yoy have changed an existing value do you want to Continue?", vbYesNo, "Continue")

If LResponse = vbYes Then
original = Target.Value
Exit Sub
Else
Target.Value = original
Exit Sub
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
original = Target.Value
End Sub


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing