+ Reply to Thread
Results 1 to 6 of 6

how can i undo After running any other macro code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    how can i undo After running any other macro code

    i have which is i fond in net

    please let me know how can i modify this code

    this undo code should work
    After Running Other Macro Codes



    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    
    
    
    
        'set your criteria here
        If Target.Column < 16 Then
    
            'must disable events if you change the sheet as it will
            'continually trigger the change event
             Application.EnableEvents = False
             Application.Undo
             Application.EnableEvents = True
    
             MsgBox "You cannot do that!"
           
         End If
    End Sub

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: how can i undo After running any other macro code

    Hi baig123,

    Unfortunately, what you want probably can not be done easily.

    Take the following example Macro:
    Sub SimpleUndo()
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    End Sub
    For example if you put 1,2,3,4 in 4 consecutive cells. Manually you can undo all 4 actions.

    Put 1,2,3,4 in 4 consecutive cells again. Run Macro SimpleUndo() several times. All that happens is the 4 toggles between 4 and whatever was in the cell previously.

    You can write a macro to log changes you make (i.e. saving the previous value). You can then write another macro to undo what was done by using the values of the changes (i.e. previous values) you saved. See the attached file that contains the following code:

    Sheet1 module:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
      Call SavePreviousValue(Target)
    End Sub
    Ordinary Code module (e.g. Module ModUnDo):
    Option Explicit
    
    Public Type myType
      sAddress As String
      vValue As Variant
    End Type
    
    Public myGblVarrayCount As Long
    Public myGblVarray() As myType
    
    Sub SimpleUndo()
      'This will undo the last value changed
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    End Sub
    
    Sub ClearCustomUndoArray()
      myGblVarrayCount = 0
      ReDim myGblVarray(1 To 1)
    End Sub
    
    Sub MyCustomUndo()
      'This will undo all stored changes
      
      Dim vValue As Variant
      Dim i As Long
      Dim sAddress As String
      
      Application.EnableEvents = False
      If myGblVarrayCount > 0 Then
        For i = myGblVarrayCount To 1 Step -1
          sAddress = myGblVarray(i).sAddress
          vValue = myGblVarray(i).vValue
          Range(sAddress) = vValue
        Next i
        Call ClearCustomUndoArray
      End If
      Application.EnableEvents = True
      
    End Sub
    
    Sub SavePreviousValue(ByVal Target As Range)
      'This will not work if more than one value is changed at a time
      '
      'This is the Event Handler called by the Worksheet_Change() event
    
      Dim myValue As Variant
      
      If Target.Count > 1 Then
        Exit Sub
      End If
      
      'Get the current value
      myValue = Target.Value
      
      'Save the previous value for later use
      Application.EnableEvents = False
      Application.Undo
      myGblVarrayCount = myGblVarrayCount + 1
      If myGblVarrayCount = 1 Then
        ReDim myGblVarray(1 To myGblVarrayCount)
      Else
        ReDim Preserve myGblVarray(1 To myGblVarrayCount)
      End If
      myGblVarray(myGblVarrayCount).sAddress = Target.Address(False, False)  '(false,false) means NO '$' signs
      myGblVarray(myGblVarrayCount).vValue = Target.Value
      
      'Restore the new value
      Target.Value = myValue
      
      'Set the focus one cell to the right of the value just changed
      Range(Target.Address).Offset(0, 1).Select
      
      Application.EnableEvents = True
      
    End Sub

    Lewis
    Attached Files Attached Files
    Last edited by LJMetzger; 11-27-2014 at 02:12 PM. Reason: Corrected errors in MyCustomUndo() and replaced attached file.

  3. #3
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    Re: how can i undo After running any other macro code

    thanks for an suggest

    but i have more macro codes in the same excel which is to run i have attached to my sheet

    i have access code which is used to access data from the database

    what i was trying to do is after accessing data from the database



    if any client try to edit first 6 columns it should undo immediatly

    it should display an output that youcant change the row

    this undo code should works after running accessing data from the database in a sheet

    it is possibale to do it Please let me know

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    
    
    
    
        'set your criteria here
        If Target.Column < 16 Then
    
            'must disable events if you change the sheet as it will
            'continually trigger the change event
             Application.EnableEvents = False
             Application.Undo
             Application.EnableEvents = True
    
             MsgBox "You cannot do that!"
           
         End If
    End Sub
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: how can i undo After running any other macro code

    I think you are trying to use the wrong tool to do what you want. I think you want to lock the cells, and protect the sheet.

    To protect the sheet:
    a. Select all the cells in the workbook > Format Cells > Protection > Uncheck Locked (i.e. allow access to all cells)
    b. Select the first 6 columns > Format Cells > Protection > Check Locked (i.e. do not all allow access to columns 1-6)
    c. Tools > Protection > Protect Sheet (to activate what you just selected).

    When you need to unlock those cells when you are downloading data from your data base you could use something like:
    Sub YourDownloadMacro()
    
    
      Sheet1.Unprotect
        
      'Your Download code here
           
      Sheet1.protect DrawingObjects:=False, Contents:=True, Scenarios:=False
        
    End Sub

  5. #5
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    Re: how can i undo After running any other macro code

    can i fixed a range columns to be protected in this code


    Sub YourDownloadMacro()
    
    
      Sheet1.Unprotect
        
      'Your Download code here
           
      Sheet1.protect DrawingObjects:=False, Contents:=True, Scenarios:=False
        
    End Sub

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: how can i undo After running any other macro code

    Yes,

    Use the following instructions from my previous post.

    To protect the sheet:
    a. Select all the cells in the workbook > Format Cells > Protection > Uncheck Locked (i.e. allow access to all cells)
    b. Select the first 6 columns > Format Cells > Protection > Check Locked (i.e. do not all allow access to columns 1-6)
    c. Tools > Protection > Protect Sheet (to activate what you just selected).
    The following tutorial may help you:
    http://www.functionx.com/excel/Lesson19.htm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need some help on keeping UNDO/REDO working after running macro's. Almost there ;-)
    By onidarbe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2013, 02:00 PM
  2. (CHANGED) Macro Undo Code
    By dmeinder in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2012, 11:16 AM
  3. Can't 'Undo' with VBA macro running
    By leopardhawk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2008, 11:51 AM
  4. [SOLVED] Running of Worksheet Change Macro breaks undo functionality.
    By Rob Manger in forum Excel General
    Replies: 1
    Last Post: 04-05-2006, 11:04 PM
  5. Cannot Undo After Running Macro
    By Simon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1