AW: VBA - zählen wie oft Zelle kopiert wurde
24.06.2014 10:37:35
Nepumuk
Hallo,
teste mal (die Anzahl der Kopiervorgänge wird in Spalte O eingetragen. Die Spalte und den Tabellennamen im Open-Event musst du natürlich anpassen).
' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************
Option Explicit
Private Sub Workbook_Open()
If ActiveSheet Is Tabelle1 Then _
Set Tabelle1.LastSelection = Selection
End Sub
' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************
Option Explicit
Private mobjLastSelection As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngIndex As Long
Dim blnNotEqual As Boolean
If Not LastSelection Is Nothing Then
If Target.Count = LastSelection.Count Then
For lngIndex = 1 To Target.Count
If Target(lngIndex).Value <> _
LastSelection(lngIndex).Value Then
blnNotEqual = True
Exit For
End If
Next
If Not blnNotEqual Then
Application.EnableEvents = False
Cells(LastSelection.Row, 15).Value = _
Cells(LastSelection.Row, 15).Value + 1
Application.EnableEvents = True
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = 0 Then Set LastSelection = Selection
End Sub
Public Property Get LastSelection() As Range
Set LastSelection = mobjLastSelection
End Property
Public Property Set LastSelection(ByRef probjLastSelection As Range)
Set mobjLastSelection = probjLastSelection
End Property
Gruß
Nepumuk