[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Excel Macro to insert multiple lines

Why does the undo button grey out after this macro is run.  I would like to have the user be able to click undo if they put the lines in the wrong part.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/23/2011 by Administrator
'

'
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveCell.Select
    ActiveSheet.Paste
End Sub

Open in new window

0
WolfManBN25
Asked:
WolfManBN25
  • 3
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Excel clears the undo stack whenever a macro is run.

You can define your own undo subroutine that can be run from the undo button.  In your specific example it would appear as follows:
 
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/23/2011 by Administrator
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    Sheets("Sheet2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveCell.Select
    ActiveSheet.Paste
    Application.OnUndo "Undo Insert", "Undo_Macro3" 'add this line to macro to call undo routine
End Sub

Sub Undo_Macro3()
    Range(Selection, Selection.Offset(4, 0)).Select
    Selection.EntireRow.Delete
End Sub

Open in new window

0
 
BusyMamaCommented:
Write a public sub that reverses the macro you just ran.

Add a line to the current macro that calls the public sub:

Application.OnUndo "Undo something", "UnDoSomething"

This will enable the Undo button to run the public sub you wrote (the reverse of your macro).
0
 
BusyMamaCommented:
Oops, sorry, didn't see the first response before submitting.  :)
0
 
Glenn RayExcel VBA DeveloperCommented:
Caveat:
I should note that the undo function will only work if run immediately after running the insert macro (Macro3).  If another cell/sheet is selected, the Undo function will act on that range instead of the original range.  

Also, if another edit is made, it adds that to the undo stack, eliminating the customized undo subroutine.
0
 
Glenn RayExcel VBA DeveloperCommented:
If this problem has been resolved, please close the question.  thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now