how to create a undo clear all button in excel

Question

I have made it clear all button but I'm having issues making a undo clear all button code for a clear all button in VBA

private sub commandbutton1_click
Sheets ("examplesheet").range ("a1:a3").value = ""

can somebody help me make a undo button for this example in the instance that I accidentally cleared the whole form?

Ben, see the answer to [this SO post](http://stackoverflow.com/questions/7004754/how-to-programmatically-code-an-undo-function-in-excel-vba). — chuff, Jul 21 '13 at 17:54
Sorry, the post answer that I suggested deals only with undoing non-VBA actions. Instead, take a look at the answer described **[here](http://www.jkp-ads.com/Articles/UndoWithVBA00.asp)**. — chuff, Jul 21 '13 at 17:59
A little over my head to be honest could you provide a example code for the code provided? — Ben Lawson, Jul 21 '13 at 18:18

score 1 · Answer 1 · answered Jul 21 '13 at 18:41

You can create a function that does something when You click the undo button.

Here is an example that uses a sheet called Undo to store the info required for the undo. The sheet can be hidden to make it cleaner.

Sub Test()
  ClearCell Cells(1, 1)
End Sub

Sub ClearCell(R As Range)
  Sheets("Undo").Range("A1") = R.Worksheet.Name
  Sheets("Undo").Range("A2") = R.Address
  Sheets("Undo").Range("A3") = R.Text
  R.Clear
  Application.OnUndo "Undo button text", "MyUndo"
End Sub

Sub MyUndo()
  Sheets(Sheets("Undo").Range("A1").Text).Range(Sheets("Undo").Range("A2").Text) = Sheets("Undo").Range("A3").Text
End Sub

You can refine it by managing a queue of actions as explained in the document suggested by chuff's comment

how to create a undo clear all button in excel

1 Answers1