Beabeiten/Rückgängig nach Makro nicht möglich?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Application.ScreenUpdating = False If [l11].Value = 0 Then Rows("15").EntireRow.Hidden = True Else Rows("15").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("17").EntireRow.Hidden = True Else Rows("17").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("19").EntireRow.Hidden = True Else Rows("19").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("21").EntireRow.Hidden = True Else Rows("21").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("23").EntireRow.Hidden = True Else Rows("23").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("25").EntireRow.Hidden = True Else Rows("25").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("27").EntireRow.Hidden = True Else Rows("27").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("29").EntireRow.Hidden = True Else Rows("29").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("31").EntireRow.Hidden = True Else Rows("31").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("33").EntireRow.Hidden = True Else Rows("33").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("35").EntireRow.Hidden = True Else Rows("35").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("37").EntireRow.Hidden = True Else Rows("37").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("39").EntireRow.Hidden = True Else Rows("39").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("41").EntireRow.Hidden = True Else Rows("41").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("43").EntireRow.Hidden = True Else Rows("43").EntireRow.Hidden = False End If If [l11].Value = 0 Then Rows("45").EntireRow.Hidden = True Else Rows("45").EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub

Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Malo,
habe da einen Text mit Erklärung und Code, aber auf Englisch, den hänge ich an.
Gruß,
Beate
Undoing a VBA Subroutine
Computer users have become accustomed to the ability to "undo" an operation. Almost every operation you perform in Excel can be undone. If you program in VBA, you may have wondered if it's possible to undo the effects of a subroutine. The answer is yes. The qualified answer is it's not always easy.
Making the effects of your subroutines undoable isn't automatic. Your subroutine will need to store the previous state so it can be restored if the user choose the Edit Undo command. How you do this will vary, depending on what the subroutine does. In extreme cases, you might need to save an entire worksheet. If your subroutine modifies a range, for example, you need only save the contents of that range.
The code below demonstrates how to enable the Edit Undo command after a subroutine is executed. The subroutine itself is very simple: it simply inserts a 0 into every cell in the current range selection. The bulk of the code is used to save the contents of the current selection.
Trying it out
To try out this example code:
1. Copy the code to an empty VBA module.
2. Enter some data into a worksheet range.
3. Select the range and execute the ZeroRange subroutine. The cells will be replaced with zeros.
4. Select the Edit Undo command. The original contents of the selection will be restored.
How it works
The OldSelection array stores the cell address and the cell contents (using a custom data type). Notice that this array is declared as a Public variable so it's available to all subroutines. The last statement in the ZeroRange subroutine specifies the text to display in the Undo menu, and the subroutine to call if this command is selected. The UndoZero routine loops through the OldSelection array and restores the values to their appropriate cells. Notice that I also store the workbook and worksheet -- which ensures that the correct cells will be restored even if the user switches out of the original worksheet.
The Undo example
'Custom data type for undoing
Type SaveRange
Val As Variant
Addr As String
End Type

' Stores info about current selection
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange


Sub ZeroRange()
'   Inserts zero into all selected cells
'   Abort if a range isn't selected
If TypeName(Selection)  "Range" Then Exit 

Sub
'   The next block of statements
'   Save the current values for undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).Addr = cell.Address
OldSelection(i).Val = cell.Formula
Next cell
'   Insert 0 into current selection
Application.ScreenUpdating = False
Selection.Value = 0
'   Specify the Undo Sub
Application.OnUndo "Undo the ZeroRange macro", "UndoZero"
End Sub



Sub UndoZero()
'   Undoes the effect of the ZeroRange 

Sub
'   Tell user if a problem occurs
On Error GoTo Problem
Application.ScreenUpdating = False
'   Make sure the correct workbook and sheet are active
OldWorkbook.Activate
OldSheet.Activate
'   Restore the saved information
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Next i
Exit Sub
'   Error handler
Problem:
MsgBox "Can't undo"
End Sub

Beliebteste Forumthreads (12 Monate)

Beabeiten/Rückgängig nach Makro nicht möglich?

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Beabeiten/Rückgängig nach Makro nicht möglich?

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread