Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

Please explain this line of code TimeStampSimple Target, "G2", "I", "mm/dd/yyyy…

  1. User

    Please explain this line of code TimeStampSimple Target, "G2", "I", "mm/dd/yyyy hh:mm:ss", False

  2. Assistant

    This line of code calls the function named "TimeStampSimple" and passes four arguments:

    1. "Target": This is a reference to the cell or range where the time stamp will be inserted.

    2. "G2": This is the default value that will be displayed in the cell if an error occurs while inserting the time stamp.

    3. "I": This specifies the column where the timestamp should be inserted.

    4. "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.

    5. 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.

  3. User

    Can I remove the default value G2 from the code

  4. 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", False
    

    In 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.

  5. 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", False
    

    End 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 = True
    

    End 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

  6. 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 = False which disables events is still set to False even 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 = False to 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 Sub
    

    By moving Application.EnableEvents = False to the beginning and Application.EnableEvents = True to 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.

  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 = True
    

    End Sub

  8. 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:

    1. 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".

    2. 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.