Consulting

Results 1 to 10 of 10

Thread: Macro Undo Command

  1. #1

    Macro Undo Command

    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

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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

  3. #3
    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!

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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.

  5. #5
    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/off...plication.undo

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

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Why would you like to program actions that are so 'wrong' they have to be 'undone' ?
    That is a controdictio in terminis.

  7. #7
    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

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Preventing making errors would improve your status better.
    Last edited by snb; 05-27-2020 at 09:57 AM.

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by 1lineatatime View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    Thank you for your advice Paul.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •