PDA

View Full Version : Macro Undo Command



1lineatatime
05-26-2020, 06:47 AM
Hello, I have a question pertaining to 'undoing' a macro command that I have coded. I created a workbook with two sheets (assignments; completed assignments) that has a list of assignments on the 'assignments' sheet. I added a conditional progress bar at the end of each assignment row on the 'assignments' sheet that determines how complete a particular assignment is for any given row that contains multiple criteria. Once the progress bar reaches 100%, the entire row makes a strike-through and fades out (this is another conditional format change I made; not essential information for my question). I then created and assigned my 'HelloWorld' macro to a button that, when pressed, will scan the 'assignments' sheet for any row(s) that contain 100%. If they do, it will automatically copy, delete, and move the next row up; and then finally paste the 100% complete row into the 'completed assignments' sheet. The only issue is that if the button is accidentally pressed by a user before intended, this command cannot be undone because Excel does not natively store changes made by macros. This can be solved by 'closing without saving' but I thought it would be nice to be able to code a macro that will undo what was previously done. Based off the research I've done so far, I think I can use 'Application.Undo' in some way but am not sure how to integrate it, since I am new to VBA. Any help would be greatly appreciated! I am using Excel 2013 on Windows 10. Below is the macro code that works well and is described above. Also, because I am new to this forum, if there is anything I missed or can do next time differently when posting a question or answer to improve others' experiences, please let me know. Thank you.


Sub HelloWorld()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim Z As Long
Dim K As Long
I = Worksheets("Assignments").UsedRange.Rows.Count
Z = Worksheets("Completed Assignments").UsedRange.Rows.Count
If Z = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Completed Assignments").UsedRange) = 0 Then Z = 0
End If
Set xRg = Worksheets("Assignments").Range("T1:T" & I) 'T is the column that contains the progress bar
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "1" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Completed Assignments").Range("A" & Z + 1) 'Copies row to other sheet
xRg(K).EntireRow.Delete 'Deletes previous row
If CStr(xRg(K).Value) = "1" Then
K = K - 1
End If
Z = Z + 1
End If
Next
Application.ScreenUpdating = True
End Sub

Logit
05-26-2020, 08:31 AM
.
Use this macro FIRST. Your CommandButton would be attached to this macro. The user
answers the question and either exits or proceeds ...


Option Explicit

Sub DataChanges()
Dim Msg As String, Title As String
Dim Config As Integer, Ans As Integer


Msg = "Are You Sure ?"
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Changes Cannot Be Undone."

Title = "Data Changes"
Config = vbYesNo + vbExclamation
Ans = MsgBox(Msg, Config, Title)


If Ans = vbYes Then HelloWorld
If Ans = vbNo Then Exit Sub
End Sub

1lineatatime
05-26-2020, 08:39 AM
Logit, thank you for responding. This maro is good, and I will take advantage of this. However, I am trying to become more integrated with specifically writing code to undo a macro command by using something like application.undo. I want to get more experienced in this specific area so that I can do write this code with other workbooks in the future; so any additional help would be greatly appreciated; thank you!

Logit
05-26-2020, 08:49 AM
.
Regarding the Application.Undo command ...

There are numerous websites with example code, instruction and sample files for download. You could start there with a small project of your own.
And as you did above .. when you run into an issue, post your code on the Forum and request assistance.

1lineatatime
05-26-2020, 12:49 PM
I actually just found that application.undo works only for non-vba commands according to Microsoft's website (I am not sure how I missed this earlier) - https://docs.microsoft.com/en-us/office/vba/api/excel.application.undo

In this regard, there has to be a way to undo the VBA command in the module that I posted above.

snb
05-27-2020, 05:55 AM
Why would you like to program actions that are so 'wrong' they have to be 'undone' ?
That is a controdictio in terminis.

1lineatatime
05-27-2020, 06:09 AM
The action of cutting a completed assignment and pasting it to a completed page is organizational and great for record keeping. I just want the option to reverse that command if something was accidentally cut and directed to the completed assignments page. It is especially useful when working in a shared workbook where people other than myself may make mistakes that they don’t know how to fix. So, the programmed macro button that reverses what they did would make their lives a lot easier... and me look very smart :)

snb
05-27-2020, 07:36 AM
Preventing making errors would improve your status better.

Paul_Hossler
05-27-2020, 08:32 AM
Hello, I have a question pertaining to 'undoing' a macro command that I have coded. I created a workbook with two sheets (assignments; completed assignments) that has a list of assignments on the 'assignments' sheet. I added a conditional progress bar at the end of each assignment row on the 'assignments' sheet that determines how complete a particular assignment is for any given row that contains multiple criteria. Once the progress bar reaches 100%, the entire row makes a strike-through and fades out (this is another conditional format change I made; not essential information for my question). I then created and assigned my 'HelloWorld' macro to a button that, when pressed, will scan the 'assignments' sheet for any row(s) that contain 100%. If they do, it will automatically copy, delete, and move the next row up; and then finally paste the 100% complete row into the 'completed assignments' sheet. The only issue is that if the button is accidentally pressed by a user before intended, this command cannot be undone because Excel does not natively store changes made by macros. This can be solved by 'closing without saving' but I thought it would be nice to be able to code a macro that will undo what was previously done. Based off the research I've done so far, I think I can use 'Application.Undo' in some way but am not sure how to integrate it, since I am new to VBA. Any help would be greatly appreciated! I am using Excel 2013 on Windows 10. Below is the macro code that works well and is described above. Also, because I am new to this forum, if there is anything I missed or can do next time differently when posting a question or answer to improve others' experiences, please let me know. Thank you.



1. One (complicated) way would be to have your macro store the original values first, then make the change(s), and then have a confirmation 'Are You Sure?' dialog box.

If 'Yes, I really do know what I'm doing' then post the changes

If 'Oops, I didn't want to do that', then put the original values back

The complicated part might be to copy the 'To Be Deleted' rows to a temp worksheet, remember the row number where they go, put them back if 'Oops'


Or


2. Highlight the rows to be deleted in red, then show the confirmation box to at least give the user a chance to change their mind

If it's really a concern, you could have a second 'Are You REALLY Sure?' message box (maybe hide the rows that will NOT be deleted??) before you make the non-UnDo-able changes


#2 would most likely be easier

1lineatatime
05-27-2020, 06:01 PM
Thank you for your advice Paul.