Conditionally Displaying a Message Box

Summary: If you want to display a message box when a particular set of criteria are met, you'll be interested in the techniques in this tip. The approach discussed here relies on the use of the Change event for a worksheet. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

You may have a need to display a message box whenever specific information is placed in a specific cell by the user. Fortunately, using the Change event for a worksheet can help you to figure out when something has been placed in a cell.

For instance, let's say that you wanted to displays a message whenever the information in cell C3 is changed. The following, added to the code window for a specific worksheet, will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Then
        MsgBox "Changed It!"
    End If
End Sub

The Change event is called, and passes the cell range to the routine. In this case, the range is assigned to the Target variable. The address of this range is then checked, and if it is equal to C3, (has to be noted in absolute terms, such as $C$3), then the message box is displayed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2553) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!

Check out ExcelTips Archives today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Conditionally Displaying a Message Box