Add date to end of cell value on change cell

[GarethSteval]

Hi everyone

Long time lurker, here's my first post

I am struggling with code, I simply can't get this to work. I want to write a macro that when you complete editing text, it adds the date in brackets once you exit the cell. This is for an activity tracking list, so you type the comment and it should automatically insert the date that the comment was logged once enter is pressed.

This is the macro:
ActiveCell.Value = ActiveCell.Value & " [" & Date & "]"

I have tried the code in the following:
Private Sub Worksheet_Change
Self-explanatory
Private Sub Worksheet_SelectionChange
Self-explanatory


The problem is it only changes the cell that you click on, not the one that you were on.
I eventually got both the above to work by referring to the above cell (not the best method though), but they ended up putting the date a million times in one cell (obviously, when it adds the date, it recognises the change and runs it again). Als

Application.OnKey "{Enter}"
It wouldn't run when it was in edit mode (well, I think that's what the problem was)

I tried:

Private Sub PolNo_KeyPress(KeyAscii As Integer)
If (KeyAscii = 13) Then
Call DateMacro
End If

Range("A1").Value = KeyAscii

End Sub

It didn't work nor did it post the button pressed to A1.

I tried searches and I just can't seem to get this to work. Any help would be greatly appreciated. Thanks!

 

[Rick Rothstein]

You need to set up a "cooperative" between the SelectionChange and Change events. Try putting this in the sheet module...

Dim SelectedCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not SelectedCell Is Nothing Then
    If Len(SelectedCell) Then
      Application.EnableEvents = False
      SelectedCell.Value = SelectedCell.Value & " [" & Date & "]"
      Application.EnableEvents = True
    End If
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = [B][COLOR="#FF0000"][SIZE=3]4[/SIZE][/COLOR][/B] Then
    Set SelectedCell = Target
  Else
    Set SelectedCell = Nothing
  End If
End Sub

Note: You did not say what column the comments were to go in, so I assumed Column D... if that is wrong, then change the red highlighted 4 to the column number for your comment column.

 

[GarethSteval]

Hi Rick

Thanks so much for the feedback. I tried the code, but it doesn't change anything (though, it also doesn't give any errors). I understand what you have written and don't see any reason for it not working (I tried typing comments in column D, and others btw).

Thanks again!

 

[Rick Rothstein]

Hi Rick

Thanks so much for the feedback. I tried the code, but it doesn't change anything (though, it also doesn't give any errors). I understand what you have written and don't see any reason for it not working (I tried typing comments in column D, and others btw).

Did you put the code in the worksheet module for the worksheet that is to have this functionality (not in a general module where macros go)? Make sure you copy everything I posted including that free-floating Dim statement. Just so you know, I tested the code before I posted it and it worked fine for me.

 

[GarethSteval]

Hi Rick

Sorry, that was my mistake, I put it in a module!

Thank you so much, this is amazing! Can't believe you managed to do this so quickly, I spent a few hours already trying different things.

Thanks for your time!

 

[Rick Rothstein]

Can't believe you managed to do this so quickly, I spent a few hours already trying different things.

I have been programming since 1981... this is not the first time I have encountered the situation you described you needed code for.

 

[GarethSteval]

Wow, you must have an incredible amount of experience! Thanks again!