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.

Project Explorer Sheet1

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

Your time is valuable. It's time to become a VBA expert.

The macro above will prevent a user from entering non-numeric values in the range A1:D4 of Sheet1. You can change the range by modifying the entry in quotes in the first 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.

Submit a comment below if you like this macro or have more questions. Who knows - my solution to your comment may become the next tutorial on!

For more complicated questions, I hope you’ll check out my VBA Consulting page. From there, I can answer all your VBA questions.

Please subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more VBA tutorials.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too

Your time is valuable. It's time to become a VBA expert.

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.