Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
capturing date for a change in a row of data
I want to capture (automatically) the date any change is made in a row of
data. Capture would be in a cell on the same row. I am not VBA proficient, can anyone help? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
capturing date for a change in a row of data
On Jul 25, 11:52*pm, Mark wrote:
I want to capture (automatically) the date any change is made in a row of data. *Capture would be in a cell on the same row. *I am not VBA proficient, can anyone help? Thanks You would need to enter a worksheet change event, so go to toolsmacrovisual basic editor. On the left hand side you should see the workbook (If you don't go to ViewProject explorer, from there expand your current workbook, you should see the different worksheets that apply to your workbook. Double click the worksheet you want this to apply to, at the top of the code you have some dropdowns, select worksheet in the first one and then select Change from the second one. What you are saying is I want my code to run everytime there is a change to the worksheet. The following should appear: Private Sub Worksheet_Change(ByVal Target As Range) Target refers to the cell that has changed. What you need to decide is which colum the date should appear... For example between the Private Sub... and End Sub... you would put: Cells(Target.row, 5) = Date .... This would mean that in column E of the row that has changed (E being the fifth column) the date would be entered. Something like... Private Sub Worksheet_Change(ByVal Target As Range) Cells(Target.Row, 5) = Date End Sub Cells is a standard notation for referencing the cell, otherwise you would use the Range notation, in which case... Private Sub Worksheet_Change(ByVal Target As Range) Range("E" & Target.row) = date End Sub Again changing "E" to the colum you want to enter the date into, this would do exactly the same thing, Date is a built in function in VBA. James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
capturing date for a change in a row of data
Thanks, that worked really well. However....I probably over simplified it.
I want a date only if there is a change, now even a click in a cell does the data addition. Is there anything I can do to check for an actual change or addition? thanks again, Mark " wrote: On Jul 25, 11:52 pm, Mark wrote: I want to capture (automatically) the date any change is made in a row of data. Capture would be in a cell on the same row. I am not VBA proficient, can anyone help? Thanks You would need to enter a worksheet change event, so go to toolsmacrovisual basic editor. On the left hand side you should see the workbook (If you don't go to ViewProject explorer, from there expand your current workbook, you should see the different worksheets that apply to your workbook. Double click the worksheet you want this to apply to, at the top of the code you have some dropdowns, select worksheet in the first one and then select Change from the second one. What you are saying is I want my code to run everytime there is a change to the worksheet. The following should appear: Private Sub Worksheet_Change(ByVal Target As Range) Target refers to the cell that has changed. What you need to decide is which colum the date should appear... For example between the Private Sub... and End Sub... you would put: Cells(Target.row, 5) = Date .... This would mean that in column E of the row that has changed (E being the fifth column) the date would be entered. Something like... Private Sub Worksheet_Change(ByVal Target As Range) Cells(Target.Row, 5) = Date End Sub Cells is a standard notation for referencing the cell, otherwise you would use the Range notation, in which case... Private Sub Worksheet_Change(ByVal Target As Range) Range("E" & Target.row) = date End Sub Again changing "E" to the colum you want to enter the date into, this would do exactly the same thing, Date is a built in function in VBA. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing The Date Of When Data Is Inputted | Excel Discussion (Misc queries) | |||
capturing date of entering data in a spreadsheet | Excel Programming | |||
Capturing Date | New Users to Excel | |||
Capturing First Change In A Cell | Excel Worksheet Functions | |||
Capturing The First Change in a Formula | Excel Worksheet Functions |