Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
We have a spreadsheet where one cell (A) has a drop down list of choices.
Cells B C D E correspond to the list. So the list is b,c,d,e and the next columns are b,c,d,e headers. If the cell A changes from one choice to another can that date the change occured be inputed into the corresponding cell? Thanks for all the help you guys give. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
Well, being a Redskins fan, I'm not sure if I should help you or not.....
You need to use a worksheet change event to do that: for example, for a change made to any cell in column A, the date when the entry is made or changed is stored in column B using this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("A:A")) Cells(myCell.Row, 2).Value = Now Cells(myCell.Row, 2).NumberFormat = "mm/dd/yy hh:mm:ss" Next myCell Application.EnableEvents = True End Sub Copy this code, right-click on the worksheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... We have a spreadsheet where one cell (A) has a drop down list of choices. Cells B C D E correspond to the list. So the list is b,c,d,e and the next columns are b,c,d,e headers. If the cell A changes from one choice to another can that date the change occured be inputed into the corresponding cell? Thanks for all the help you guys give. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
I am summoning a lot of strength to respond to a redskins fan....
Thanks for the quick response below. I added the code. The change takes place in column Q and the date appears in column B. How do I tell the code that if say the drop down equals "Invoice phase" that it adds the date to column V? Or if the drop down choice is "Approval phase" it puts the date in column T? The code is great, I guess I am not aware of out to manipulate if further. "Bernie Deitrick" wrote: Well, being a Redskins fan, I'm not sure if I should help you or not..... You need to use a worksheet change event to do that: for example, for a change made to any cell in column A, the date when the entry is made or changed is stored in column B using this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("A:A")) Cells(myCell.Row, 2).Value = Now Cells(myCell.Row, 2).NumberFormat = "mm/dd/yy hh:mm:ss" Next myCell Application.EnableEvents = True End Sub Copy this code, right-click on the worksheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... We have a spreadsheet where one cell (A) has a drop down list of choices. Cells B C D E correspond to the list. So the list is b,c,d,e and the next columns are b,c,d,e headers. If the cell A changes from one choice to another can that date the change occured be inputed into the corresponding cell? Thanks for all the help you guys give. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
I don't really follow football anymore - more of a soccer guy these days
;-) Anyway, try this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("Q:Q")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("Q:Q")) If myCell.Value = "Invoice Phase" Then Cells(myCell.Row, 22).Value = Now Cells(myCell.Row, 22).NumberFormat = "mm/dd/yy hh:mm:ss" End If If myCell.Value = "Approval Phase" Then Cells(myCell.Row, 20).Value = Now Cells(myCell.Row, 20).NumberFormat = "mm/dd/yy hh:mm:ss" End If Next myCell Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... I am summoning a lot of strength to respond to a redskins fan.... Thanks for the quick response below. I added the code. The change takes place in column Q and the date appears in column B. How do I tell the code that if say the drop down equals "Invoice phase" that it adds the date to column V? Or if the drop down choice is "Approval phase" it puts the date in column T? The code is great, I guess I am not aware of out to manipulate if further. "Bernie Deitrick" wrote: Well, being a Redskins fan, I'm not sure if I should help you or not..... You need to use a worksheet change event to do that: for example, for a change made to any cell in column A, the date when the entry is made or changed is stored in column B using this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("A:A")) Cells(myCell.Row, 2).Value = Now Cells(myCell.Row, 2).NumberFormat = "mm/dd/yy hh:mm:ss" Next myCell Application.EnableEvents = True End Sub Copy this code, right-click on the worksheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... We have a spreadsheet where one cell (A) has a drop down list of choices. Cells B C D E correspond to the list. So the list is b,c,d,e and the next columns are b,c,d,e headers. If the cell A changes from one choice to another can that date the change occured be inputed into the corresponding cell? Thanks for all the help you guys give. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
Well I am glad you watch real "football" now.
Thank you so much for the help. I did as you said and add some more lines and it works perfectly. This will be a big help for us. Thanks again, I am very grateful. "Bernie Deitrick" wrote: I don't really follow football anymore - more of a soccer guy these days ;-) Anyway, try this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("Q:Q")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("Q:Q")) If myCell.Value = "Invoice Phase" Then Cells(myCell.Row, 22).Value = Now Cells(myCell.Row, 22).NumberFormat = "mm/dd/yy hh:mm:ss" End If If myCell.Value = "Approval Phase" Then Cells(myCell.Row, 20).Value = Now Cells(myCell.Row, 20).NumberFormat = "mm/dd/yy hh:mm:ss" End If Next myCell Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... I am summoning a lot of strength to respond to a redskins fan.... Thanks for the quick response below. I added the code. The change takes place in column Q and the date appears in column B. How do I tell the code that if say the drop down equals "Invoice phase" that it adds the date to column V? Or if the drop down choice is "Approval phase" it puts the date in column T? The code is great, I guess I am not aware of out to manipulate if further. "Bernie Deitrick" wrote: Well, being a Redskins fan, I'm not sure if I should help you or not..... You need to use a worksheet change event to do that: for example, for a change made to any cell in column A, the date when the entry is made or changed is stored in column B using this code: Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("A:A")) Cells(myCell.Row, 2).Value = Now Cells(myCell.Row, 2).NumberFormat = "mm/dd/yy hh:mm:ss" Next myCell Application.EnableEvents = True End Sub Copy this code, right-click on the worksheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP "ihatetheredskins" wrote in message ... We have a spreadsheet where one cell (A) has a drop down list of choices. Cells B C D E correspond to the list. So the list is b,c,d,e and the next columns are b,c,d,e headers. If the cell A changes from one choice to another can that date the change occured be inputed into the corresponding cell? Thanks for all the help you guys give. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in one cell generates (todays) date in another
Thanks again, I am very grateful. You're quite welcome. Thanks for letting me know that you succeeded... Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add todays date (static) to the current active cell using m | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
How to put todays date in one cell then the next cell adds x days | Excel Discussion (Misc queries) | |||
Put todays date in a cell by checking a box!! | Excel Discussion (Misc queries) | |||
In Excel, Get todays date in a cell | Excel Discussion (Misc queries) |