How to use a worksheet change event to enforce numerical entries in appropriate worksheet cells or ranges. Watch the video below:
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
