Instead of using Data Validation, you can use VBA to allow only numbers in a cell or a range in Excel. By restricting what the user can enter, you can improve the performance of your spreadsheet. This Code Library tutorial will show you how to prevent a user from entering non-numeric characters in a cell.
Restrict Entries to Numbers Only
To allow only numbers in a cell or range, open your VBA editor and double-click the sheet you want to restrict entries in on your Project Explorer pane. Remember, the shortcut to open your Project Explorer window from your VBA editor is Ctrl+R.
In this example, I want to limit entries to only numbers in Sheet1 of my workbook.
Once you double-click Sheet1, paste the following macro into your editor:
Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Application.EnableEvents = False For Each cell In Target If Not Application.Intersect(cell, Range("A1:D4")) Is Nothing Then If Not IsNumeric(cell.Value) Then cell.Value = vbNullString End If End If Next cell Application.EnableEvents = True End Sub
Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
The macro above will prevent a user from entering non-numeric values in the range
If statement. You can change which sheet has the restriction by pasting the code in another sheet object in your VBA editor.
Once the user tries to submit a cell with non-numeric text, the target cell immediately changes to an empty string. The beautiful thing is this even works when someone tries to copy and paste a value into a restricted cell!
I hope you like this example. I got inspired to post this tutorial by a comment from a reader, Anthony, on my GetAsyncKeyState article.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.