CODEWITHSUNDEEP
excelvba
Ben Lawson
Ben Lawson

Reputation: 1

how to create a undo clear all button in excel

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?

Upvotes: 0

Views: 1795

Answers (1)

stenci
stenci

Reputation: 8501

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

Upvotes: 1

Related Questions