Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Date
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Date
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Date
On May 18, 8:52*am, Greg D <Greg
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate including a date so that the date appears as a date | Excel Worksheet Functions | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |