Time stamp cells on single mouse click

[SJV9]

Hi everyone!

Can someone help me on how I could get a time stamp on column G each time cells on column B is clicked?

I tried inserting both commandbuttons or labels and set them to invisible so the text underneath is the thing to be seen. I got it to stamp the time on a single click alright however I would also want to be able to select the value on the cell underneath the label/button.

 

[Kenneth Hobson]

Right click the sheet tab, View Code, and paste:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iRange As Range
  If Target.Cells.Count <> 1 Then Exit Sub
  Set iRange = Intersect(Target, Range("B:B"))
  If iRange Is Nothing Then Exit Sub
  Application.EnableEvents = False
  Range("G" & Target.Row).Value = Now()
  Application.EnableEvents = True
End Sub

 

[SJV9]

Wow Kenneth that was quick! Is this static time stamp? I wouldnt want it to change each day I open the file.

Also, I need it to do the same on column R when column C is clicked.


I appreciate your quick response and efficiency!

 

[Kenneth Hobson]

Now() is the current date and time.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iRange As Range, dt As Date
  dt = Now()
  If Target.Cells.Count <> 1 Then Exit Sub
  Set iRange = Intersect(Target, Range("B:B"))
  If iRange Is Nothing Then Exit Sub
  Application.EnableEvents = False
  Range("G" & Target.Row).Value = dt
  Range("R" & Target.Row).Value = dt
  Columns("G:G").AutoFit
  Columns("R:R").AutoFit
  Application.EnableEvents = True
End Sub

 

[SJV9]

It time stamps on both columns G and R each time column B is clicked. Can you tweak it a little bit so it becomes:

- time stamp on G when B i clicked
- time stamp on R when C is clicked

Also, I enabled iterative calculation and set it to 1 but it still makes the time stamps dynamic...

 

[Kenneth Hobson]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iRange As Range
  If Target.Cells.Count <> 1 Then Exit Sub
  Set iRange = Intersect(Target, Range("B:C"))
  If iRange Is Nothing Then Exit Sub
  Application.EnableEvents = False
  Select Case Target.Column
    Case 2
      Range("G" & Target.Row).Value = Now()
        Columns("G:G").AutoFit
    Case 3
      Range("R" & Target.Row).Value = Now()
      Columns("R:R").AutoFit
  End Select
  Application.EnableEvents = True
End Sub

 

[SJV9]

This works for me. Thank you so much Kenneth!!! You're a genius! ray:

Have a great day!

 

[Kenneth Hobson]

I recommend reading this article about crossposting: http://www.excelguru.ca/node/7

If you don't want the cells to update if they have a value:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iRange As Range
  If Target.Cells.Count <> 1 Then Exit Sub
  Set iRange = Intersect(Target, Range("B:C"))
  If iRange Is Nothing Then Exit Sub
  Application.EnableEvents = False
  Select Case True
    Case Target.Column = 2 And IsEmpty(Range("G" & Target.Row))
      Range("G" & Target.Row).Value = Now()
        Columns("G:G").AutoFit
    Case Target.Column = 3 And IsEmpty(Range("R" & Target.Row))
      Range("R" & Target.Row).Value = Now()
      Columns("R:R").AutoFit
  End Select
  Application.EnableEvents = True
End Sub

 

[SJV9]

Hi Kenneth, thanks for the tip!

In relation to your vb formula, is it possible if can we set it to apply only to cells which have values so that there should be no time stamp coming from blank cells?

 

[Kenneth Hobson]

Which cells empty? I did that for columns R and G already.