Prev: Looking for Formula
Next: Vertical Header in Excel
From: Greg D Greg on 18 May 2010 09:52
We have a spreadsheet that we would like to automatically add the date in the
first cell of each row when information is entered into another cell in that
row. The date would need to remain static once added. Any suggestions?
From: Dave Peterson on 18 May 2010 10:11
This worksheet_Change event will update the date in column A each time there's a
change anywhere else in the row.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRngToCheck As Range
Dim myIntersect As Range

With Me
Set myRngToCheck = .Range("B1", .Cells(.Cells.Count))

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

Set myIntersect = myIntersect.EntireRow

Application.EnableEvents = False
For Each myCell In Intersect(.Columns(1), myIntersect).Cells
With myCell
'just the date
.NumberFormat = "mmmm dd, yyyy"
.Value = Date
'or use time and date
.NumberFormat = "mmmm dd, yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
Application.EnableEvents = True

End With

End Sub

If you really want the date to be static -- once it's been set, it won't change
even if more changes are made to the row, you could check that cell to see if
it's empty first.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRngToCheck As Range
Dim myIntersect As Range

With Me
Set myRngToCheck = .Range("B1", .Cells(.Cells.Count))

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

Set myIntersect = myIntersect.EntireRow

Application.EnableEvents = False
For Each myCell In Intersect(.Columns(1), myIntersect).Cells
With myCell
If IsEmpty(.Value) Then
'just the date
.NumberFormat = "mmmm dd, yyyy"
.Value = Date
'or use time and date
.NumberFormat = "mmmm dd, yyyy hh:mm:ss"
.Value = Now
End If
End With
Next myCell
Application.EnableEvents = True

End With

End Sub

Each of these are worksheet events.

That means that you can rightclick on the worksheet tab that should have this
behavior and select View|Code.

Then paste the version of the code you want into the code window that just
opened (usually on the right hand side).

ps. You could use the date or date and time. Delete the lines that you don't
want from the procedure.



On 05/18/2010 08:52, Greg D wrote:
> We have a spreadsheet that we would like to automatically add the date in the
> first cell of each row when information is entered into another cell in that
> row. The date would need to remain static once added. Any suggestions?
From: Jason on 18 May 2010 10:31
On May 18, 8:52 am, Greg D <Greg D...(a)discussions.microsoft.com>
wrote:
> We have a spreadsheet that we would like to automatically add the date in the
> first cell of each row when information is entered into another cell in that
> row. The date would need to remain static once added. Any suggestions?

I don't think there's a function that will work for this, but you
could use the following vba:

If Range("A" & Target.Row).Value = "" Then
Range("A" & Target.Row).Value = Now()
End If

To assign the code, right-click on the tab and select View Code. The
vba editor will open. Click the drop-down list that contains the text
"(General)" and select Worksheet. Then click the drop down list in
the upper right that contains the text "(Declarations)" and select
Change. You should then be presented with this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Paste the code from above between the two lines of text. It should
look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A" & Target.Row).Value = "" Then
Range("A" & Target.Row).Value = Now()
End If
End Sub

Close the vba editor window to get back to the spreadsheet.

Hope this helps.
Jason
 | 
Pages: 1
Prev: Looking for Formula
Next: Vertical Header in Excel