Just another knowledge sharing place

  • Join 1 other follower

  • Read Counter

    • 426 hits

Validate Excel Cell Input

Posted by sqlnow on June 3, 2010

You are doing many critical calculations in a single excel spreadsheet in our daily work, but what if you entered invalid data into the sheet? All the formulas entered will through some error. So can you really validate user inputs in excel?

Yes, you can!! This post demonstrates how to restrict incorrect inputs into a particular column. Here I will demonstrate how to validate a column where you can enter only Numeric values. Just follow these simple steps:

Press ALT + F11 key combination to open Visual Basic Editor >> double click on the Sheet you want to validate from the Project Window. Paste the below VBA code and save the spreadsheet and you are done with the requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
 'do nothing if multiple cells edited
 If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
With Target
If .Row > 1 Then
If IsNumeric(Cells(.Row, 1)) = False Then
MsgBox "Not a valid integer value", vbInformation
End If
End If
End With
End Sub

Isn’t it interesting?

I am not allowed to attach excel files in my posts or else I could have provided you the sample macro for this. Please leave your valuable comment here.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: