Kleine Frage zu "großem" Code
Tim
ich verwende folgenden Code, um ein Backup-Logfile zu kreiren, was soweit bestens klappt.
Wie kann ich hier eine einzige Zelle (F1201) aussschließen, so dass Änderungen in dieser Zelle keinen Eintrag im Logfile verursachen ?
Option Explicit
Dim mstrOld(1 To 1600, 1 To 30) As String
Private Sub Worksheet_Change(ByVal Target As Range)
' creates log file for Database changes
If Target.Column > 15 Then Exit Sub
Dim objCell As Range
If Not Intersect(Target, Range("A2:O1600")) Is Nothing Then
For Each objCell In Intersect(Target, Range("A2:O1600"))
Cells(objCell.Row, 16) = Date
Next
End If
Dim strDatei As String, strText As String
Dim strZeit As String, strUser As String, strZelle As String, strOld As String, strNeu As _
String
Dim intFile As Integer, rng As Range
Const strDELIM As String = " | " 'logfile delimiter
Const lenUser As Integer = 18 'min. number of signs for User column
Const lenAdresse As Integer = 5 'min. number of signs for Cell column
Const lenWert As Integer = 50 'min. number of signs for Old Value and New Value columns
Const FormatZeit As String = "YYYY/MM/DD hh:mm " 'format for time stamp
intFile = FreeFile
With ThisWorkbook
'saving location
strDatei = "C:\Users\Notebook\Desktop\My Documents\LogFiles\" & "LogFile" & "_" _
& Left(.Name, InStrRev(.Name, ".") - 1) & ".txt"
End With
Open strDatei For Append As #intFile
If LOF(intFile) = 0 Then
'log file headers
With Application.WorksheetFunction
strZeit = "Date & Time"
strZeit = strZeit & VBA.Space(.Max(0, Len(FormatZeit) - Len(strZeit)))
strUser = "User"
strUser = strUser & VBA.Space(.Max(0, lenUser - Len(strUser)))
strZelle = "Cell"
strZelle = strZelle & VBA.Space(.Max(0, lenAdresse - Len(strZelle)))
strOld = "Old Value"
strOld = strOld & VBA.Space(.Max(0, lenWert - Len(strOld)))
strNeu = "New Value"
strNeu = strNeu & VBA.Space(.Max(0, lenWert - Len(strNeu)))
strText = strZeit & strDELIM & strUser & strDELIM & strZelle & strDELIM & _
strOld & strDELIM & strNeu & strDELIM
End With
Print #intFile, strText
strText = String(Len(strZeit), "-") & strDELIM & String(Len(strUser), "-") & strDELIM _
_
& String(Len(strZelle), "-") & strDELIM _
& String(Len(strOld), "-") & strDELIM & String(Len(strNeu), "-") & strDELIM
Print #intFile, strText
End If
For Each rng In Target.Cells
If rng.Value mstrOld(rng.Row, rng.Column) Then
With Application.WorksheetFunction
strZeit = Format(Now, "YYYY/MM/DD hh:mm ")
strUser = Environ("username")
strUser = strUser & VBA.Space(.Max(0, lenUser - Len(strUser)))
strZelle = VBA.Replace(rng.Address, "$", "")
strZelle = strZelle & VBA.Space(.Max(0, lenAdresse - Len(strZelle)))
strOld = mstrOld(rng.Row, rng.Column)
strOld = strOld & VBA.Space(.Max(0, lenWert - Len(strOld)))
strNeu = IIf(rng.Value = "", "! deleted !", rng.Value)
strNeu = strNeu & VBA.Space(.Max(0, lenWert - Len(strNeu)))
strText = strZeit & strDELIM & strUser & strDELIM & strZelle & strDELIM & _
strOld & strDELIM & strNeu & strDELIM
End With
Print #intFile, strText
End If
Next
Close #intFile
End Sub
VG und vielen Dank im Voraus, Tim