excel help

[rickmuti]

I am in need of a formula to develope a timestamp of sorts. I want to
check cell A1 for any data and then automatically input the date and
time the data was entered. I have tried the formula

=IF(A1<>"",NOW(),"")

The problem with the now command is that it updates with each
calulation. I need the date and time to be fixed.


Rick

 

[JulieD]

Hi

you need to either enter the date & time manually
control & ; (semi-colon) will give you the date
and
control & shift & ; will give you the time
you can put these two in the same cell by pressing spacebar between them
.....

or you can use worksheet_change code which runs whenever the value in A1 is
changed (in the following code the date & time will be put into cell B1)
-----

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = "$A$1" And Not IsNull(Target.Value) Then
Target.Offset(0, 1).Value = Now
End If

End Sub
---

to use this code, right mouse click on the sheet tab of the sheet you're
dealing with and choose view code,
copy and paste the code on the right hand side screen then use ALT & F11 to
return to your workbook.

 

[rickmuti]

Julie, I know the manual keystrokes will work, but I am trying to make
this so any non-computer user will be able to work it. What I am doing
is reading a bar code into cell A and need it timestamped. Is there a
way to generate the manual keystrokes within a formula. Know matter how
I use the NOW command it updates to the new time.
Thanks!!!
Rick

 

[Guest]

Julie's response also had some VBA code to address the situation, and I think
that's about the only way to accomplish your goal.

her code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.address = "$A$1" And Not IsNull(Target.Value) Then
Target.Offset(0, 1).Value = Now
End If

End Sub

Julie's code works only for cell A1. You may want to change the If
statement to

If Target.column = 1 And Not IsNull(Target.Value) Then

Change the "column = 1" to reflect your column of interest, and this will
then work for any cell in that column

 

[rickmuti]

Duke and Julie, I tried the VBA code and have it working. The only
problem is I do not have to enter any data in column A to get the date
in column B. All I need to do is position the curser on a cell in
column A and the date is input. What the problem is, if I move the
curser up to previously input cells, it changes the date. Is there a
way to have this work where you have to have data in each of the
respective column A cells?

Rick

 

[Guest]

Make sure you put the code in the right procedure. It sounds as if your
procedure is named

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

and not

Private Sub Worksheet_Change(ByVal Target As Range)

The former fires every time you select a new cell, the latter fires only
when a cell is changed

 

[rickmuti]

Duke, thanks!! That's got it.

Rick

 

[Gord Dibben]

Rick

Try this version

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Or this version to keep previously stamped dates if editing column A

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

 

[JulieD]

Duke, good catch, i wouldn't have had an idea