Thursday, January 01, 2009

How to Undo the last action using Excel VBA

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.

Sub Undo_Invalid_Entry()

If Target.Address = "$A$2" Then

If IsNumeric(Target) = False Then

Application.EnableEvents = False

Application.Undo

End If

End If

Application.EnableEvents = True

End Sub

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.

No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group