Worksheet Change Event to Enter Numerical Values

How to use a worksheet change event to enforce numerical entries in appropriate worksheet cells or ranges. Watch the video below:

Worksheet Change Event to Enter Numerical Values

Using worksheet change event to allow only numerical entries in an Excel worksheet. If a user enters text, for example, in a worksheet cell, he should be warned, the entered value in the worksheet range should be cleared and the same cell selected again to allow the user to enter a numerical value only.

To write the VBA code we can right click on our worksheet name and select view code to come to the Visual Basic Editor. If we go to the Visual Basic Editor via the Developer tab and Visual Basic then we have to click on Project Explorer and finally double click on the relevant worksheet. From the object area we select worksheet and from the procedure area we select ‘change’ as shown in the video.

Between the following two lines of code we write our code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Finally our code will look like so:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

If InStr(Target.Address, “:”) > 0 Or InStr(Target.Address, “;”) Or InStr(Target.Address, “,”) > 0 Then Exit Sub

For Each MyRange In Sheet1.Range(“B2:B100”)

If Target = MyRange Then

If Not IsNumeric(Target) Or InStr(1, Target, “,”) > 0 Then

MsgBox “Enter only a number in the cell. No commas please.” MyRange.ClearFormats

MyRange.ClearContents

MyRange.Activate

MyRange.NumberFormat = “0.00”

End If

End If

Next MyRange

End Sub

Worksheet Change Event to Force Users to Enter Numerical Values Only
Worksheet Change Event to Force Users to Enter Numerical Values Only

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.