1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel undo

Discussion in 'Business Applications' started by scouse13, Jan 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    hi all,
    i am trying to figuure out how to undo a last entry using vba, the last entry also alters other cells that are linked to it using formulas.
    the code i am using is below

    cell L4 is a starting figure-(cell locked, this figure is entered on a different sheet)
    cell E7 is where the user makes entries-(cell unlocked)
    cell H10 is the running total-(cell locked)
    cell L22 is the remaining figure-(cell locked)

    basically if the starting figure L4 is 500, users enter figures in E7(these entries are copied to column 37(AK) as they are entered, cells H10 and L22 alter accordingly.
    IF THE USER ENTERS AN INCORRECT FIGURE IN E7 I WOULD LIKE TO BE ABLE, USING VBA TO UNDO THE LAST ENTRY, THUS CHANGING L22 AND H10 BACK TO THE WHAT THEY
    WOULD HAVE BEEN IF THE ENTERY HAD NOT BEEN MADE.
    i have tried using the inbuilt undo button in excel to no avail.

    any idea's please

    Private Sub worksheet_change(ByVal Target As Range)
    Dim wb As Workbook, ws As Worksheet, wsk As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Darts2players")
    Set wsk = Sheets("Darts2players")

    Application.ScreenUpdating = False

    CommandButton1.Visible = False
    CommandButton2.Visible = False
    CommandButton3.Visible = False

    If Not Intersect(Target, Range("E7")) Is Nothing Then
    ActiveSheet.Protect
    ActiveSheet.Unprotect
    ws.Range("H10").Value = ws.Range("H10").Value + ws.Range("E7").Value
    ActiveSheet.Protect
    X = WorksheetFunction.CountA(Columns(37))
    ActiveSheet.Unprotect
    Cells(X + 1, 37) = Target
    ActiveSheet.Protect
    End If
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I am fairly certain that there is a no way to undo a VBA issued command. Sorry.

    You might be able to get by using the SelectionChange event to set the cells original value equal to a variable or you could even write the last value to an unused cell. You would then create a custom "undo" macro to retrieve the previous value from the cell and paste into the current cell address which can be obtained using Target.Address(0, 0)

    Keep in mind the SelectionChange code below needs to be stored at the worksheet level while the "undo" macro would need to be stored at the module level.

    Rollin
     
  3. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    thanks for our quick reply "rollin"

    could u please expand on what u have said

    using the SelectionChange event to set the cells original value equal to a variable or you could even write the last value to an unused cell. You would then create a custom "undo" macro to retrieve the previous value from the cell and paste into the current cell address which can be obtained using *Target.Address(0, 0)*. Keep in mind the SelectionChange code below needs to be stored at the worksheet level while the "undo" macro would need to be stored at the module level.

    could u show an example of what u mean, i cold then play around with it and it if works it may help somebody else in the future
    thanks
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I guess I am a little confused. Will the cell value be changed programatically through the macro or will the user physically select the cell and enter a value manually? If the change is done manually by a user you can undo the action using the VBA command Application.Undo to revert to the old value. If the cell value is changed programatically it can't be undone using VBA. Does that make sense?

    Rollin
     
  5. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    hi "rollin"
    in answer to your question the cell E7 are manually entered and the cells H10 and L22 are programatically altered throu the code.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977650

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice