Forum Discussion

James818's avatar
James818
Brass Contributor
Apr 30, 2024

My pre-populated date keeps changing to the day I open the file

Greetings everyone.

 

I got an excel sheet that populates the date when someone selects a drop down of names.   This means that person completed a task. 

 

The problem is, when I open the file on another day, the dates change to today.

I just need that date to be the original date when it was originally selected and not change to today unless the name changes.  

 

Thank you in advance for your assistance.

 

=IF(OR(J5<>"", K5<>""), TODAY(), "")

 

 

 

  • James818 

    I've added this code to your file which returned the expected result in my sample.

    Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngBereich As Range
    
    Set rngBereich = Range("J4:K100")
    
    If Target.Cells.Count > 1 Then GoTo done
    
    If Not Application.Intersect(Target, rngBereich) Is Nothing Then
    
    If Target.Column = 10 And Target.Value <> "" Then
    Target.Offset(0, 2).Value = Date
    Else
    If Target.Column = 11 And Target.Value <> "" Then
    Target.Offset(0, 1).Value = Date
    Else
    End If
    End If
    
    End If
    
    done:
    Application.EnableEvents = True
    Exit Sub
    
    End Sub

     

    • James818's avatar
      James818
      Brass Contributor
      I appreciate the response.

      Just out of curiosity, is there a way to avoid VBA? I ask only because this will be a shared file, in the past had a bad experience with not everyone user getting the VBA to work without an update.

Resources