How to Validate Keypress in Excel VBA
A combination of the Undo method and the Worksheet events can be used to validate Text input.
It can be used with Worksheet events effectively. For example, the following code will allow the user to enter only numerals in A2. If any non-numeric character is entered, the code reverts the contents of the range to the last stored value.
If Target.Address = "$A$2" Then
If IsNumeric(Target) = False Then
Application.EnableEvents = False
Application.EnableEvents = True
Try using this event from Worksheet_Change event. The procedure will resist any entry other than numerals in "A2 and will restore the original text if found otherwise
Note: Application.Undo method undoes only the last action taken by the user before running the macro. This line must be the first line in the macro.
It cannot be used to undo Visual Basic commands.