XL: Visual Basic Example for Validating Data on Entry

ID: Q139663

The information in this article applies to:

SUMMARY

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

NOTE: The macro provided below will run in Microsoft Excel 97 but you do not have to use the macro to validate data that is entered into cells. Microsoft Excel 97 has a new Data Validation feature that allows you to specify what data is valid for certain cells and ranges.

MORE INFORMATION

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
The following sample macro generates the default system sound when valid data is entered in to 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, use these steps to set up your Microsoft Excel workbook:

1. To define the range A1:A10 on Sheet1: 

    a. On Sheet1, select the range A1:A10.

    b. Point to Name on the Insert menu, and click Define.

    c. In the Names In Workbook box, type "WatchArea", without quotation
       marks, and click OK.
2. Insert a new module sheet into your workbook.

3. In the module sheet, enter the following macros: 

      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 you want to "watch." Also, modify the "WatchArea" name
   in the WatchIT macro to the name of the defined area on the sheet
   specified in the auto_open routine. Use the Application.OnEntry code to
   "watch" data entry in all open workbooks and worksheets. You can also
   use the WorkBooks collection to qualify a specific workbook if desired.
4. Save the workbook, close and re-open it.

This will run the Auto_Open macro that sets an internal flag for Microsoft 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 on Sheet1, you will hear the default system sound. If you enter data that is outside the specified range (in this case any value that is not in the range 0- 100), a dialog box will be displayed.

REFERENCES

For more information about Data Validation, click the Index tab in Microsoft Excel 97 Help, type the following text 

   data validation, preventing incorrect entry
and then double-click the selected text to go to the "Prevent entry of incorrect data in a cell" topic.

For more information about OnEntry or Intersect, click the Index tab in Microsoft Excel 7.0 Help, type either of the following 

   OnEntry

   -or-

   Intersect
and then double-click the selected text to go to the desired topic.

Additional query words: 5.00 7.00 8.00 97 Data Entry Validate 

Keywords          : kbprg kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999