Keep time from changing when change a cell in next row

**gav0101** · 05-05-2007, 11:53 PM

I'm looking for a way to automatically add the date and time to a cell when I add data to another cell. so

I'm creating a log spreadsheet. During our test we log all the incidents, problems, or just plain notes that occur. The spreadsheet consist of three columns, Col b = date and time, col C=type entry, col D=description. When I enter a type in column c, I want to populate column b in the same row with the date and time. I tried using the NOW function, but when I type in a value in column C on the next row, it changes all the dates/times in both rows. Is there a way to hold the date/time once it has been populated.

**mudraker** · 05-06-2007, 12:08 AM

You need a worksheet change event macro

The following macro once installed correctly will run everytime you make a change on the worksheet. It is coded so that if the change is in column C then it will add the now() time in column B for the same row

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

**gav0101** · 05-06-2007, 12:30 AM

Thanks for your help, but nothing seems to happen. I believe I followed your instructions. I copied the macro, opened VBA code screen, pasted the macro, saved and closed VBA code screen. When I select one of the valid values in C or just type the value the date and time don't appear.

**mudraker** · 05-06-2007, 01:00 AM

If the macro is installed on the correct VBA module sheet it should work as required.

Can you attach a copy of your workbook reply message so I can check it.

If the book contains info that you can not share, delete all data from the sheets before posting. Do not delete the sheets.

You will need to zip the book as you can not upload Excel files.

If the book has multiple sheets then I will also need to know the sheet name for the sheet that you are trying to use this macro on.

**gav0101** · 05-06-2007, 01:22 AM

I found your posting from earlier and saw the comments about setting enableevents to true. This was my problem. I also copied your code where it clears the date/time when if I clear column c value.

Now I'm getting date/time, but the date is 1/0/00.

Working on zipping. I've never zipped a file before.

**mudraker** · 05-06-2007, 01:33 AM

I did not suggest the Enableevents as I assumed you were new to these types of macros and the enableevents would still be active on your pc. Looks like I had better stop assuming things.

For zipping files I use Winzip. I know Windows can do it as well but as I have never used Windows to zip files I can not advice you on the process

**gav0101** · 05-06-2007, 01:44 AM

Spreadsheet is attached. I added a reset of enableevnts everytime the worksheet is saved just is case. I figured with autosave on resetting the enable events wouldn't hurt. I also added one that I can execute if needed.

**mudraker** · 05-06-2007, 02:02 AM

The macro you copied was coded to add the time value only to a cell.

As you require the Date & time change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

**gav0101** · 05-06-2007, 12:35 PM

Thanks for all your help. You are correct I'm a novice at these types of macros. After my first posting, but before your reply, I found your answers in the "Holding Time Value" thread. You had already answered/solved all my problems in that thread. Thanks again.