how to undo a macro?

[maximas]

I have a protected sheet. I written macros to add and delete rows. if a user accidentally deleted a row using the macro, how would you undo what he did.
ie.
Sub Delete()
ActiveSheet.Unprotect password:="test"
ActiveCell.EntireRow.Delete
ActiveSheet.Protect password:="test"
End Sub

 

[Zathras]

The delete macro could capture all of the data in the row before doing the delete. Then a different macro would be able to re-insert the deleted data when invoked by the user.

Depending on the nature of the data, you could store the data in (global) arrays or in a hidden sheet created for the purpose.

An array could be used to keep track of where each row was deleted from, allowing multiple undo capability. If a hidden sheet is used, it should be added the first time it is needed and then removed before saving the worksheet.

If only a single undo is needed, then the data could be saved in individual variables or in an array of variant type.

 

[maximas]

how could you capture the data? If the data is in a row with text and numbers, how would you save that in an array?
Could you give an example, it will be greatly appreciated.
Thank you.

 

[Zathras]

Something like this: (I'll leave the security aspects to you, since you seem to have a good grasp of that.)

Note that this will save both data and formulas. (As long as the formulas only reference cells on the same row it's ok, otherwise deleting the row causes other problems.)

Paste the following into a new module:
========================================================
Option Explicit

' Global variables to save current row data and location
' Note: Only one "undo" is possible.
' Make into 2-dimension array to save multiple rows
Dim SavedData(255) As Variant
Dim SavedRow As Double

Sub DeleteWithSave()
' Sample top level macro to delete a row with saving for undo
SaveCurrentRow
ActiveCell.EntireRow.Delete
End Sub

Sub SaveCurrentRow()
' Saves contents of current row in global variables
Dim r As Range, c As Range
Dim nCol As Integer
SavedRow = ActiveCell.Row
Set r = Range("A" + Trim(SavedRow) + ":IV" + Trim(SavedRow))
nCol = 0
For Each c In r
SavedData(nCol) = c.Formula
nCol = nCol + 1
Next
Set r = Nothing
End Sub

Sub RestorePreviousRow()
' Restores previously deleted row from global variables
Dim r As Range, c As Range
Dim nCol As Integer
Rows(Trim(SavedRow) + ":" + Trim(SavedRow)).Select
Selection.Insert Shift:=xlDown
Set r = Range("A" + Trim(SavedRow) + ":IV" + Trim(SavedRow))
nCol = 0
For Each c In r
c.Formula = SavedData(nCol)
nCol = nCol + 1
Next
Set r = Nothing
End Sub

 

[maximas]

thanks a bunch!