Microsoft KB Archive/141181

= How to Make Sure That Only Numbers Are Entered in a Column =

Article ID: 141181

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q141181



This article also applies to:
 * Microsoft Excel for Windows 95 7.0a|7.0a
 * Microsoft Excel for Windows 95 7.0|7.0
 * Microsoft Excel for Windows 5.0c|5.0c
 * Microsoft Excel for the Macintosh 5.0|5.0
 * Microsoft Excel for the Macintosh 5.0a|5.0a



SUMMARY
This article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that you can use to ensure that a certain type of data entry occurs. This macro requires a user to enter only numbers in a specific column on a worksheet.

NOTE: Microsoft Excel 97 and Microsoft Excel 98 offer a new tool called Data Validation. This tool provides the same functionality as the sample macros in this article with an expanded range of restrictions and validation message options.

For additional information on Data Validation, please see the following article in the Microsoft Knowledge Base:

157484 XL97: Error When Using Validation Drop-Down List Box



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. The following sample macros restrict the data type in column C of sheet 1 to numbers. Sub Auto_Open Sheets("Sheet1").OnEntry = "numbers" End Sub

Sub numbers If ActiveCell.Column = 3 And Not IsNumeric(ActiveCell.Value) Then MsgBox "Enter a number." ActiveCell.Value = "" ' Clears contents of active cell. End If  End Sub To restrict the data type in another column, replace the number 3 in the line that begins: If ActiveCell.Column = 3 with the number that represents the column you want. For example, use 1 for column A, 2 for column B, and so on.

