| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
auto enter date when another cell populated?
I have an s/sheet with order data: I need to create a macro or formula so
that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
auto enter date when another cell populated?
You need a macro, try this. right click on the sheet where you want this to
happen and paste this code in. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("C1:C65536"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -2) .NumberFormat = "dd mmm yyyy" .Value = Now End With With .Offset(0, -1) .NumberFormat = "hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Was that OK? Mike "zim" wrote: I have an s/sheet with order data: I need to create a macro or formula so that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
auto enter date when another cell populated?
No... I must have missed something. Pasting the code only displays the
code... no activity in the cells. Is there not a function that would recognize when a cell is not blank and date/time stamp another cell? (Sorry - still a newbie and trying to understand... and not familiar with VisualBasic). - Thanks! -- zim "Mike" wrote: You need a macro, try this. right click on the sheet where you want this to happen and paste this code in. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("C1:C65536"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -2) .NumberFormat = "dd mmm yyyy" .Value = Now End With With .Offset(0, -1) .NumberFormat = "hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Was that OK? Mike "zim" wrote: I have an s/sheet with order data: I need to create a macro or formula so that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
auto enter date when another cell populated?
Zim,
Sorry I read my instructions again and I've misled you. Right click on the sheet tab and then click view code and paste the code in there. With the code entered there when you type in column C. The date & time will then be entered in columns A&B respectively. Mike "zim" wrote: No... I must have missed something. Pasting the code only displays the code... no activity in the cells. Is there not a function that would recognize when a cell is not blank and date/time stamp another cell? (Sorry - still a newbie and trying to understand... and not familiar with VisualBasic). - Thanks! -- zim "Mike" wrote: You need a macro, try this. right click on the sheet where you want this to happen and paste this code in. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("C1:C65536"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, -2) .NumberFormat = "dd mmm yyyy" .Value = Now End With With .Offset(0, -1) .NumberFormat = "hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Was that OK? Mike "zim" wrote: I have an s/sheet with order data: I need to create a macro or formula so that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
auto enter date when another cell populated?
right click sheet tabview codeinsert this.test
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub Application.EnableEvents = False Target.Offset(, -2) = Date Target.Offset(, -1) = Time Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "zim" wrote in message ... I have an s/sheet with order data: I need to create a macro or formula so that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
auto enter date when another cell populated?
Thanks much for your help! I'm good to go!!
-- zim "Don Guillett" wrote: right click sheet tabview codeinsert this.test Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub Application.EnableEvents = False Target.Offset(, -2) = Date Target.Offset(, -1) = Time Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "zim" wrote in message ... I have an s/sheet with order data: I need to create a macro or formula so that when data is entered into i.e. Column C, the date is automatically entered into Column A and the time into Column B. The dates / times need to remain static / constant and not update when the file is reopened. Any suggestions? (I've tried an = If(isblank etc.)... with Ctrl+; and Ctrl+Shift+; but my date defaults to 1/1/00). Thanks for your help! -- zim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Payment cell populated based on date formula | Excel Discussion (Misc queries) | |||
| Auto enter date when data in enter in another cell | Excel Worksheet Functions | |||
| Auto insert Date & Time when another cell is populated | Excel Discussion (Misc queries) | |||
| How to auto-enter date when cell is clicked? | Excel Discussion (Misc queries) | |||
| Can a cell have a drop down list and can also be auto populated | Excel Worksheet Functions | |||
Linear Mode
