How to Automatic date entry in Excel? 2

[6656]

How to program Excel worksheet when a value is entered in a worksheet, the cell to the right side is filled with the recorded today's date? The date value will saved and should not be changed as =TODAY() by daily.

 

[RobBroekhuis]

On the worksheet's code, make a new worksheet_change event handler (by using the dropdown lists),
and add as code into the handler:

target.offset(0,1)= date()
target.offset(0,1).numberformat="mm/dd/yy" (or whatever you prefer)

Note that this will update the date every time a value is changed. This can be tweaked to do other things if necessary.
Rob

 

[xlbo]

Using the same mehtod as Rob but slightly different

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Target.Offset(0, 1).Value = Format(Date, "dd/mm/yy&quot
Application.EnableEvents = True
End Sub

Rob - your code will cause an error as without enableevents = false, the code will loop until it gets to column IV Rgds
~Geoff~

 

[RobBroekhuis]

Yes, I guess it would
Despite my best intentions, I still don't test every bit of code I suggest here. Thy star is on its way.
Rob

 

[xlbo]

cheers Rob - I don't test all code code either- just don't have the time - I think Top of the stars for this forum says a lot for you though - think we'll let this one slip ;-) Rgds
~Geoff~

 

[6656]

Thanks Rob & Geoff,

Your code works closely that I want. How to modify your code that doing fellowing action.

1. When entering a new data in Column A (or add a new row), the date automatic entry will be coresponded in Column H only.

2. There is no change in Column H when any of other columns changes.

Thanks again!

Mike

 

[6656]

Thanks Rob & Geoff for your help,

Your code works closely that I want. How to modify your code that doing fellowing action.

1. When entering a new data in Column A (or add a new row), the date automatic entry will be coresponded in Column H only.

2. There is no change in Column H when any of other columns changes.

Thanks again!

Mike

 

[RobBroekhuis]

A slight modification to Geoff's code will take care of that:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.column=1 then
Application.EnableEvents = False
Target.Offset(0, 7).Value = Format(Date, "dd/mm/yy&quot
Application.EnableEvents = True
end if
End Sub

Rob

 

[xlbo]

Have a star Rob - that'll work nicely Rgds
~Geoff~

 

[6656]

Thank you again, supper stars!

There is a small note: when hit delete key in a cell of column A, the date also entered/update in column H. How to void it? Does it need to add as =IF(isblank(a3),"&quot) on Column H or some code in VBA code or using Worksheet_Onentry instead of Worsheet_change?

 

[xlbo]

There is no "On_Entry" event and to be honest, if you are capturing changes to a spreadsheet , it can be as useful to capture deletions as well as insertions. Anyway, here is a tweak of Rob's code to NOT enter the date when something is deleted:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.column=1 then
if target.text <> &quot;&quot; then
Application.EnableEvents = False
Target.Offset(0, 7).Value = Format(Date, &quot;dd/mm/yy&quot
Application.EnableEvents = True
end if
end if
End Sub
Rgds
~Geoff~