Stop UDF/code from updating time when cell is edited

[buttergoose]

Hi!
I've found great help in this forum, but now I need help I couldn't find in existing threads.

I have used this code, to generate an automatic time and date in an excel sheet my work uses for logging activity. Very simple; event text goes in column G, date generates per auto in column A and C.
Now, question;
When I fill an event, and trigger the auto fill for date and time, how can I stop the date/time cells from updating if I come back later and edit the text in column G (and hence prompting a new auto update with current date/time)? I only need one static entry of date and time for when the event text was entered, and then I want it to not change at all.

This is the code I've used and it works splendid for everything a part from this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Columns("G")) Is Nothing Then
If Target.Row > 1 And Target.Offset(, 2) = "" Then
Target.Offset(, -6) = Date
Target.Offset(, -5) = Time
End If
End If
End If
End Sub


Suggestion on what to add/change to get where I wanna be?

Many thanks!

 

[Georgiboy]

Welcome to the forum, you could try the below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Not Intersect(Target, Columns("G")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 2) = "" Then
                If Target.Offset(, -6) = "" Then
                    Target.Offset(, -6) = Date
                    Target.Offset(, -5) = Time
                End If
            End If
        End If
    End If
End Sub

 

[buttergoose]

Welcome to the forum, you could try the below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Not Intersect(Target, Columns("G")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 2) = "" Then
                If Target.Offset(, -6) = "" Then
                    Target.Offset(, -6) = Date
                    Target.Offset(, -5) = Time
                End If
            End If
        End If
    End If
End Sub

Thank you!
I tried copy pasting your code to my sheet but the time (and date surely, but that of course won't change every second haha) is still updating if I change the previously filled G column text...

 

[buttergoose]

Thank you!
I tried copy pasting your code to my sheet but the time (and date surely, but that of course won't change every second haha) is still updating if I change the previously filled G column text...

To add to this, my guess would be that I need some type of code string that checks if the date and time cells are filled, and if they are then to not mess with them at all... I just don't know how to do that specifically

 

[Georgiboy]

As 'Target.Offset(, -6) = Date' is placing the date in the cell the is offset -6 columns from target.

This row:

If Target.Offset(, -6) = "" Then

Will check to see if there is already something in the date cell, if there is then it will not update the date and time.

Check the code you have used because I edited my post about one minute after I posted because I used '<>' instead of '=' by mistake in my original post.

 

[buttergoose]

As 'Target.Offset(, -6) = Date' is placing the date in the cell the is offset -6 columns from target.

This row:

If Target.Offset(, -6) = "" Then

Will check to see if there is already something in the date cell, if there is then it will not update the date and time.

Check the code you have used because I edited my post about one minute after I posted because I used '<>' instead of '=' by mistake in my original post.

I didn't catch that <> either haha

Thank you so much, it's doing what I want it to now.
Greatly appreciated!

 

[Georgiboy]

You're welcome, thanks for the feedback.

Silly mistake from me...