Insert date when the line was first typed

[MAButler]

I have Todays date inserted into Column J when something is typed in Column A, and removed if I delete...... this works well......

But somtimes I need to change the cell in Column A and then the date changes...... what I need is it to stay with the date created....

This is the code i have so far.......

If Target.Column = 1 And Target.Cells.Count = 1 Then
Select Case Target.Cells.Value
Case Else
Target.Offset(0, 9).Value = Sheet2.Cells(1, 1)
End Select
If Len(Trim(Target.Value)) = 0 Then
Cells(Target.Row, "J").Value = ""
End If

End If
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C:G")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)

 

[Gary McMaster]

It sounds like it would be a simple logic statement.

Psuedo code:

If cell A <> "" and cell J <> "" then Exit Sub

Gary

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Range("A:A"), Target)
            cell.Value = UCase(cell.Value)
            If Not IsEmpty(cell) And IsEmpty(cell.Offset(, 9)) Then
                cell.Offset(0, 9).Value = Date
            Else
                cell.Offset(0, 9).ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[MAButler]

AlphaFrog:-

That clears the cell, what i need is the cell not to change......

 

[AlphaFrog]

It clears the date in column J if you clear the cell in column A.

It adds a date to column J if you enter something in column A

It does nothing if you change an existing value in column A and there is already a date in column J

 

[MAButler]

It clears the date in column J if you clear the cell in column A.

It adds a date to column J if you enter something in column A

It does nothing if you change an existing value in column A and there is already a date in column J

Yes it clears the date in column J if you clear the cell in column A.

Yes It adds a date to column J if you enter something in column A.

But sorry if you change an existing value in column A and there is already a date in column J it deletes the date and leaves it empty.....

The data in column A is entered from a drop down list......

 

[AlphaFrog]

Sorry. You're correct.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Range("A:A"), Target)
            cell.Value = UCase(cell.Value)
            If Not IsEmpty(cell) And IsEmpty(cell.Offset(, 9)) Then
                cell.Offset(0, 9).Value = Date
            [COLOR="Red"]ElseIf IsEmpty(cell) Then[/COLOR]
                cell.Offset(0, 9).ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[MAButler]

Have i added it into the original correctly.......

The first part adds inititals of user, the middle adds a reference number and the last bit is the date.....

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 And Target.Cells.Count = 1 Then
   
      UsersWindowLoginName = Environ("username")
      
      Select Case UsersWindowLoginName
         Case Sheet2.Cells(3, 3): Init = Sheet2.Cells(2, 3)
         Case Sheet2.Cells(3, 4): Init = Sheet2.Cells(2, 4)
         Case Sheet2.Cells(3, 5): Init = Sheet2.Cells(2, 5)
         Case Sheet2.Cells(3, 6): Init = Sheet2.Cells(2, 6)
         Case Sheet2.Cells(3, 7): Init = Sheet2.Cells(2, 7)
         Case Sheet2.Cells(3, 8): Init = Sheet2.Cells(2, 8)
         Case Else: Init = UsersWindowLoginName
      End Select
      
      If Len(Trim(Target.Value)) = 0 Then
         Cells(Target.Row, "H").Value = ""
      Else
         Cells(Target.Row, "H").Value = Init
      End If
   End If
   
   If Target.Column = 1 And Target.Cells.Count = 1 Then
       Select Case Target.Cells.Value
       Case Sheet2.Cells(6, 3)
           Target.Offset(0, 8).Value = Sheet2.Cells(7, 3)
        Case Sheet2.Cells(6, 4)
            Target.Offset(0, 8).Value = Sheet2.Cells(7, 4)
        Case Sheet2.Cells(6, 5)
            Target.Offset(0, 8).Value = Sheet2.Cells(7, 5)
        Case Sheet2.Cells(6, 6)
           Target.Offset(0, 8).Value = Sheet2.Cells(7, 6)
        Case Else
            Target.Offset(0, 8).Value = Sheet2.Cells(7, 7)
        End Select
    If Len(Trim(Target.Value)) = 0 Then
         Cells(Target.Row, "I").Value = ""
    End If
    End If

Dim cell As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Range("A:A"), Target)
            cell.Value = UCase(cell.Value)
            If Not IsEmpty(cell) And IsEmpty(cell.Offset(, 9)) Then
                cell.Offset(0, 9).Value = Date
            Else
                cell.Offset(0, 9).ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub

 

[MAButler]

Thanks..... its fantastic