logfile excel

[shidan]

goedemorgen,

ik probeer een logfile te maken die weergeeft als iemand een verandering doet in mijn file
(cellen overschrijven, wissen,...)
ik gebruik hiervoor onderstaande code (staat in het te loggen sheet)

ik zie de logging maar de 'PreviousValue' is niet ingevuld?
weet iemand hoe ik dit kan oplossen?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String

    sLogFileName = "C:\logger.txt"

 On Error Resume Next ' Turn on error handling
    If Target.Value <> PreviousValue Then
        ' Check if we have an error
        If Err.Number = 13 Then
           PreviousValue = 0
        End If
        ' Turn off error handling
        On Error GoTo 0
        sLogMessage = Now & " - " & Application.username & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value
        
        nFileNum = FreeFile                                       ' next file number
        Open sLogFileName For Append As #nFileNum    ' create the file if it doesn't exist
        Print #nFileNum, sLogMessage                          ' append information
        Close #nFileNum                                            ' close the file
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

 

[edmoor]

Declareer PreviousValue als Variant buiten de Subs.

 

[shidan]

Goedemorgen,

ik begrijp niet zo goed wat je bedoeld.
is dit niet wat je wilt zeggen?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

 

[edmoor]

[COLOR="#FF0000"]Dim PreviousValue As Variant[/COLOR]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String

    sLogFileName = "C:\logger.txt"

 On Error Resume Next ' Turn on error handling
    If Target.Value <> PreviousValue Then
        ' Check if we have an error
        If Err.Number = 13 Then
           PreviousValue = 0
        End If
        ' Turn off error handling
        On Error GoTo 0
        sLogMessage = Now & " - " & Application.username & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value
        
        nFileNum = FreeFile                                       ' next file number
        Open sLogFileName For Append As #nFileNum    ' create the file if it doesn't exist
        Print #nFileNum, sLogMessage                          ' append information
        Close #nFileNum                                            ' close the file
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

 

[shidan]

hartelijk bedankt