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”)

Next MyRange

End Sub