XL2000: Visual Basic Macro for Validating Data upon Entry

To automate a task or perform an operation when data is entered into a specific range in a Microsoft Excel worksheet, you can use the OnEntry and Intersect methods in a Visual Basic for Applications macro. For example, you can use this type of macro to validate data entry by verifying that a value is within a specified range.

NOTE: The following sample macro runs in Excel 2000, but you do not need to use the macro to validate data that is entered into cells. Excel has a data-validation feature that allows you to specify what data is valid for certain cells and ranges.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS The following sample macro generates the default system sound when valid data is entered into a defined area of a worksheet. In this example, valid data is defined as a numeric value between the range of 0 and 100.

To use the following macros, follow these steps to set up your Excel workbook:

1. To define the range A1:A10 in Sheet1, follow these steps:
   1. On Sheet1, select the range A1:A10.
   2. On the Insert menu, point to Name, and then click Define.
   3. In the Names In Workbook box, type WatchArea, and then click OK.
2. Press ALT+F11 to open the Visual Basic Editor.
3. On the Insert menu, click Module.
4. In the module sheet, type or paste the following code:

   Sub auto_open()
      'Set the WatchIT macro to run when data is entered into Sheet1
      ThisWorkbook.Sheets("Sheet1").OnEntry = "WatchIT"
   End Sub
   					

   Sub WatchIT()
      Dim isect As Excel.Range
      Set isect = Application.Intersect(Range(ActiveCell.Address), _
         Range("WatchArea"))
      If isect Is Nothing Then
         'Do Nothing
      Else
        'You just entered into the defined area "WatchArea" on
        '"Sheet1" add other the desired action code here or call
        'another routine.
        'Example below will alert the user if the data value is less
        'than 0 or greater than 100 or is not a number and clear the
        'entry. It will also give a confirmation beep when valid
        'data is entered.
         If (Val(ActiveCell.Value) < 0 _
            Or Val(ActiveCell.Value) > 100) _
            Or Not IsNumeric(ActiveCell.Value) Then
            ActiveCell.Clear
            MsgBox "The data value must be a Number between 0 and 100"
         End If
      Beep
      End If
   End Sub
   					

   NOTE: To customize this macro, change the "Sheet1" sheet name to the name of the sheet that you want to watch. Also, modify the "WatchArea" name in the WatchIT macro to the name of the defined area in the sheet specified in the auto_open routine. Use the Application.OnEntry code to watch data entry operations in all open workbooks and worksheets. You can also use the WorkBooks collection to qualify a specific workbook if you want.
   
   
5. Save the workbook, close it, and then re-open it.

Performing these steps runs the Auto_Open macro, which sets an internal flag for Excel to run the WatchIT macro whenever you enter data into the WatchArea range of cells.

When you enter appropriate data in the range A1:A10 in Sheet1, you will hear the default system sound. If you enter data that is outside of the specified range (0-100), a dialog box is displayed.

For additional information about getting help with Visual Basic for Applications, click the article number below to view the article in the Microsoft Knowledge Base: For more information about the Intersect method, click Microsoft Visual Basic Help on the Help menu, type intersect method in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.