Option Explicit
Public WithEvents wshAny As Worksheet
Private Sub wshAny_Change(ByVal Target As Range)
Dim zeile As Integer
Dim Blattname As String
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Changes")
Blattname = ActiveSheet.Name
Application.ScreenUpdating = False
Application.EnableEvents = False
zeile = Worksheets("Changes").UsedRange.Rows.Count + 1
With ws
'Worksheets("Aenderungen").Unprotect
.Cells(zeile, 1).Value = Now
.Cells(zeile, 2).Value = ActiveSheet.Name
.Cells(zeile, 3).Value = Target.Address
.Cells(zeile, 4).Value = AlterWert
.Cells(zeile, 5).Value = Target.Value
'Worksheets("Aenderungen").Protect
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub wshAny_SelectionChange(ByVal Target As Range)
AlterWert = Target.Value
End Sub
In ein normales Modul:
Modul: Modul1
Option Explicit
Public AppObject As New clsEvents
Public AlterWert
in die Module der Tabellen, die überwacht werden sollen:
Option Explicit
Private Sub Worksheet_Activate()
Set AppObject.wshAny = ActiveSheet
End Sub
Private Sub Worksheet_Deactivate()
Set AppObject.wshAny = Nothing
End Sub
Code eingefügt mit VBA in HTML 2.0.0.3size>
und natürlich ein Tabellenblatt namens "Changes" einfügen.
Gruß
Kawensmann
Option Explicit
Public WithEvents wshAny As Worksheet
Private Sub wshAny_Change(ByVal Target As Range)
Dim zeile As Integer
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Changes")
Application.ScreenUpdating = False
Application.EnableEvents = False
With ws
zeile = .UsedRange.Rows.Count + 1
'.Unprotect
.Cells(zeile, 1).Value = Now
.Cells(zeile, 2).Value = ActiveSheet.Name
.Cells(zeile, 3).Value = Target.Address
.Cells(zeile, 4).Value = AlterWert
.Cells(zeile, 5).Value = Target.Value
'.Protect
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub wshAny_SelectionChange(ByVal Target As Range)
AlterWert = Target.Value
End Sub
[size=8]Code eingefügt mit [url=http://vbahtml.origo.ethz.ch]
VBA in HTML 2.0.0.3[/url][/size]