1

I have a simple bit of VBA code which I wrote, which just deletes three columns in a sheet when a button is pushed.

When the button is pushed, the columns are deleted. This part works fine. What I would like to do is make this action reversable via the STOCK undo button in Excel. Currently, the deletion of these rows is not able to be undone by any means (that I am aware of), which means that if the button is pushed on accident then the sheet has to be exited and re-opened.

Here is my current code, please feel free to critique it as well, I am not very familiar with VBA best practices, although I am learning. 

Private Sub CommandButton1_Click()
Dim Alias_Adds As Worksheet
Set Alias_Adds = Sheets("Alias_Adds")

MSG1 = MsgBox("Are you ready to delete the validation columns? (A, F, and G?)", vbYesNo, "")

If MSG1 = vbYes Then
With Alias_Adds
        Alias_Adds.Columns("A").EntireColumn.Delete 'this is the line that is cauing the error
        Alias_Adds.Columns("E:F").EntireColumn.Delete
    End With
Else
  MsgBox "Ok, let's wait a bit to delete those columns"
End If

End Sub
Community
  • 1
  • 1
  • 2
    I do no believe this is a duplicate. The linked question wants to use an additional VBA button to cancel an action performed by his first VBA button. I want my VBA actions to be reversible via Excel's stock UNDO button. –  Apr 22 '14 at 12:46
  • 1
    @me how- I did read through the link you posted and I found it very helpful. –  Apr 24 '14 at 13:08

1 Answers1

0

Because Excel VBA does not support a generic undo of its own actions, YOU must build the safety feature into your logic.

Rather than simply deleting the columns, first copy them to an unused location. The data is then available for retrieval later-on.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • So there is absolutely no way to make my VBA actions reversible via the stock undo button? If not, where would you recommend saving the data too (from a best practice standpoint), should I keep it contained within 3 separate arrays using VBA (is this even possible), or would it be a better practice to just save it to a hidden sheet in the workbook and overwrite it every time the button is pushed? –  Apr 22 '14 at 12:55
  • Your choice.........use a hidden sheet or or use columns in the same sheet or use internal VBA arrays – Gary's Student Apr 22 '14 at 12:59
  • Alright, I think I can hack it by saving to another sheet, but just for leanings sake, could you show me with some code of what it would look like using arrays? Would doing this all internally via VBA be slower than the other methods –  Apr 22 '14 at 13:04
  • 1
    It would be faster using VBA arrays @Adam J – David Zemens Apr 22 '14 at 13:52
  • @AdamJ Indeed arrays are faster, but be careful: http://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba – Ioannis Apr 22 '14 at 15:00
  • Thanks for that note, I will remember that arrays are a faster option. I think I am going to solve this problem without arrays though. I will post back when my code is complete. –  Apr 22 '14 at 15:03