Please explain this line of code TimeStampSimple Target, "G2", "I", "mm/dd/yyyy…
- User
Please explain this line of code TimeStampSimple Target, "G2", "I", "mm/dd/yyyy hh:mm:ss", False
- Assistant
This line of code calls the function named "TimeStampSimple" and passes four arguments:
-
"Target": This is a reference to the cell or range where the time stamp will be inserted.
-
"G2": This is the default value that will be displayed in the cell if an error occurs while inserting the time stamp.
-
"I": This specifies the column where the timestamp should be inserted.
-
"mm/dd/yyyy hh:mm:ss": This is the format of the timestamp that will be inserted. It will be in the format of month/day/year hour:minute:second.
-
False: This parameter is optional and indicates whether the function should show a message box to confirm that the timestamp has been added. In this case, it is set to "False" which means that no message box will be displayed.
-
- User
Can I remove the default value G2 from the code
- Assistant
Yes, you can remove the default value (G2) from the code if you don't need it. Here is the modified code:
TimeStampSimple Target, "I", "mm/dd/yyyy hh:mm:ss", FalseIn this new code, the second argument "G2" has been removed and the remaining arguments are shifted to the left. The first argument now points to the target cell or range where the time stamp will be inserted.
- User
Private Sub Worksheet_Change(ByVal Target As Range)
' START OF NEW CODE Dim row As Long Dim row2 As Long Dim company As String Dim invoiceValue As Variant Dim dict As Object Set dict = CreateObject("Scripting.Dictionary")
' Check only on changes in column D If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False 'Prevent infinite loop
' Loop through all rows with data in Column A For row = 2 To Range("A" & Rows.Count).End(xlUp).row company = Trim(Range("A" & row).Value) invoiceValue = Range("D" & row).Value
' Add company name and invoice value to the dictionary If Len(company) > 0 And Not dict.exists(company & invoiceValue) Then dict.Add company & invoiceValue, row End If Next row
' Check if the new invoice value already exists in the dictionary If dict.exists(Trim(Range("A" & Target.row).Value) & Target.Value) Then row2 = dict(Trim(Range("A" & Target.row).Value) & Target.Value) 'Prompt user with message box and delete new value in column D if duplicate exists If row2 <> Target.row Then MsgBox "Duplicate found in row " & row2 & ". New value in column D has been deleted." _ , vbExclamation Range("D" & Target.row).ClearContents End If End If
Application.EnableEvents = True 'Enable events
'END OF NEW CODE
If Target.Column = 7 Then
TimeStampSimple Target, "I", "mm/dd/yyyy hh:mm:ss", FalseEnd If
End Sub
Sub TimeStampSimple( _ ByVal Target As Range, _ ByVal SourceFirstCellAddress As String, _ ByVal TimeColumnString As String, _ Optional ByVal TimeFormat As String = "mm/dd/yyyy hh:mm:ss", _ Optional ByVal OverwriteTimeStamps As Boolean = False)
' The Source range is the single-column range from the first Source cell ' to the bottom-most cell of the worksheet. ' When a change happens in any cell of the Source range: ' If OverwriteTimeStamps is set to True, it will write the time stamp. ' If OverwriteTimeStamps is set to False, it will write the time stamp ' only once i.e. if the cell in the same row of the Time column is blank. Dim sfCell As Range Dim rgSource As Range Dim rgChange As Range Dim ChangeTimeColumnOffset As Long With Target.Worksheet Set sfCell = .Range(SourceFirstCellAddress) Set rgSource = sfCell.Resize(.Rows.Count - sfCell.row + 1) Set rgChange = Intersect(rgSource, Target) If rgChange Is Nothing Then Exit Sub ' no changed cells in Source range ChangeTimeColumnOffset _ = .Columns(TimeColumnString).Column - sfCell.Column End With Dim rgTime As Range: Set rgTime = rgChange.Offset(, ChangeTimeColumnOffset) Dim rgTimeCells As Range If OverwriteTimeStamps Then Set rgTimeCells = rgTime Else Dim tCell As Range For Each tCell In rgTime.Cells If Len(CStr(tCell.Value)) = 0 Then ' the Time cell is blank; combine If rgTimeCells Is Nothing Then Set rgTimeCells = tCell Else Set rgTimeCells = Union(rgTimeCells, tCell) End If 'Else ' the Time cell is not blank; do nothing End If Next tCell If rgTimeCells Is Nothing Then Exit Sub ' no blank cells End If Dim TimeStamp As String: TimeStamp = Format(Now, TimeFormat) Application.EnableEvents = False ' to not retrigger the event rgTimeCells.Value = TimeStamp Application.EnableEvents = TrueEnd Sub
In the codes above, the line TimeStampSimple Target, "I", "mm/dd/yyyy hh:mm:ss", False worked unti I added new code above it. The new code is between the statements START OF NEW CODE and END OF NEW CODE. Do you know why the TimeStamp is no longer working
- Assistant
The reason why the TimeStamp is no longer working after adding the new code is because you have disabled events in the new code. The line
Application.EnableEvents = Falsewhich disables events is still set toFalseeven after the new code. Disabling events prevents the Worksheet_Change event from being triggered, and as a result, the TimeStampSimple function is not being called when the user makes changes in Column 7.To fix this issue, you can move the line
Application.EnableEvents = Falseto the beginning of the Worksheet_Change sub before the START OF NEW CODE statement. This will disable events before running the main codes and will also prevent the events from being re-triggered.Here is the modified code with this change:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'Disable events before running the code If Target.Column = 7 Then TimeStampSimple Target, "I", "mm/dd/yyyy hh:mm:ss", False End If ' START OF NEW CODE Dim row As Long Dim row2 As Long Dim company As String Dim invoiceValue As Variant Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Check only on changes in column D If Target.Column <> 4 Then Exit Sub ' Loop through all rows with data in Column A For row = 2 To Range("A" & Rows.Count).End(xlUp).row company = Trim(Range("A" & row).Value) invoiceValue = Range("D" & row).Value ' Add company name and invoice value to the dictionary If Len(company) > 0 And Not dict.exists(company & invoiceValue) Then dict.Add company & invoiceValue, row End If Next row ' Check if the new invoice value already exists in the dictionary If dict.exists(Trim(Range("A" & Target.row).Value) & Target.Value) Then row2 = dict(Trim(Range("A" & Target.row).Value) & Target.Value) 'Prompt user with message box and delete new value in column D if duplicate exists If row2 <> Target.row Then MsgBox "Duplicate found in row " & row2 & ". New value in column D has been deleted." _ , vbExclamation Range("D" & Target.row).ClearContents End If End If 'END OF NEW CODE Application.EnableEvents = True 'Enable events after running the code End SubBy moving
Application.EnableEvents = Falseto the beginning andApplication.EnableEvents = Trueto the end of the code, the Worksheet_Change event is disabled while running all the code between them, but enables it again when the codes have been executed. This will ensure that the TimeStampSimple function is called when the user makes changes in Column 7. - User
When I moved the TimeStamp to the top as in your suggestion, I am now getting a runtime error 1004 Method Range of object. Worksheet failed, on this line Set sfCell = .Range(SourceFirstCellAddress), within Sub TimeStampSimple.
Here are the codes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then TimeStampSimple Target, "I", "mm/dd/yyyy hh:mm:ss", False End If
Dim row As Long Dim row2 As Long Dim company As String Dim invoiceValue As Variant Dim dict As Object Set dict = CreateObject("Scripting.Dictionary")
' Check only on changes in column D If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False 'Prevent infinite loop
' Loop through all rows with data in Column A For row = 2 To Range("A" & Rows.Count).End(xlUp).row company = Trim(Range("A" & row).Value) invoiceValue = Range("D" & row).Value
' Add company name and invoice value to the dictionary If Len(company) > 0 And Not dict.exists(company & invoiceValue) Then dict.Add company & invoiceValue, row End If Next row
' Check if the new invoice value already exists in the dictionary If dict.exists(Trim(Range("A" & Target.row).Value) & Target.Value) Then row2 = dict(Trim(Range("A" & Target.row).Value) & Target.Value) 'Prompt user with message box and delete new value in column D if duplicate exists If row2 <> Target.row Then MsgBox "Duplicate found in row " & row2 & ". New value in column D has been deleted." _ , vbExclamation Range("D" & Target.row).ClearContents End If End If
Application.EnableEvents = True 'Enable events
End Sub
Sub TimeStampSimple( _ ByVal Target As Range, _ ByVal SourceFirstCellAddress As String, _ ByVal TimeColumnString As String, _ Optional ByVal TimeFormat As String = "mm/dd/yyyy hh:mm:ss", _ Optional ByVal OverwriteTimeStamps As Boolean = False)
' The Source range is the single-column range from the first Source cell ' to the bottom-most cell of the worksheet. ' When a change happens in any cell of the Source range: ' If OverwriteTimeStamps is set to True, it will write the time stamp. ' If OverwriteTimeStamps is set to False, it will write the time stamp ' only once i.e. if the cell in the same row of the Time column is blank. Dim sfCell As Range Dim rgSource As Range Dim rgChange As Range Dim ChangeTimeColumnOffset As Long With Target.Worksheet Set sfCell = .Range(SourceFirstCellAddress) Set rgSource = sfCell.Resize(.Rows.Count - sfCell.row + 1) Set rgChange = Intersect(rgSource, Target) If rgChange Is Nothing Then Exit Sub ' no changed cells in Source range ChangeTimeColumnOffset _ = .Columns(TimeColumnString).Column - sfCell.Column End With Dim rgTime As Range: Set rgTime = rgChange.Offset(, ChangeTimeColumnOffset) Dim rgTimeCells As Range If OverwriteTimeStamps Then Set rgTimeCells = rgTime Else Dim tCell As Range For Each tCell In rgTime.Cells If Len(CStr(tCell.Value)) = 0 Then ' the Time cell is blank; combine If rgTimeCells Is Nothing Then Set rgTimeCells = tCell Else Set rgTimeCells = Union(rgTimeCells, tCell) End If 'Else ' the Time cell is not blank; do nothing End If Next tCell If rgTimeCells Is Nothing Then Exit Sub ' no blank cells End If Dim TimeStamp As String: TimeStamp = Format(Now, TimeFormat) Application.EnableEvents = False ' to not retrigger the event rgTimeCells.Value = TimeStamp Application.EnableEvents = TrueEnd Sub
- Assistant
The runtime error 1004 occurs when the SourceFirstCellAddress argument passed to the TimeStampSimple function is not a valid cell reference. The error message suggests that the current implementation of the code is failing to set the SourceFirstCellAddress variable properly and is throwing this error.
To fix this error, you should ensure that the SourceFirstCellAddress argument passed to the TimeStampSimple function refers to a valid cell on the sheet.
Please check the following:
-
Verify that the SourceFirstCellAddress passed to the TimeStampSimple function is a valid cell address. It should be a set to a string variable that contains the cell address. E.g. "A1".
-
Check that the worksheet name of the Target cell is the same as the worksheet name of the SourceFirstCellAddress cell. Ensure that the Target cell belongs to the same worksheet where the SourceFirstCellAddress cell is located.
If the SourceFirstCellAddress variable doesn't refer to a valid cell address, the Set sfCell = .Range(SourceFirstCellAddress) line in the TimeStampSimple function will fail and throw a runtime error.
-