Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Displaying Messages When Automatic Data Changes

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.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Lloyd    26 Dec 2015, 21:15
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
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.