auto fill date and time

[LLOYD6664]

Hi all

I would like to enter data in cell a and i would like cell b to automatically enter the date and cell c enter the time.
i would like this to do for rows 1 to 50
can any one help i am a novice at excel vba

[AndyLitch]

Put this in the WORKSHEET code module

Please Login or Register  to view this content.

[TMS]

@Andy: you need to switch off event handling if you are going to make a change in a Worksheet Change Event handler. And there are Date and Time variables that return the system date and time respectively, so you don't need to manipulate Now.

Regards, TMS

[AndyLitch]

Thanks for that TM - I forgot about the positive feedback loop ... I shall amend the code accordingly

[LLOYD6664]

i have cut and paste this in but nothing happens when i enter data in column a any idea what i am doing wrong ?

[AndyLitch]

Did you put it in the worksheet code module of the applicable worksheet ? It Has to be in the right worksheet module.

[TMS]

It's a worksheet change event and needs to go in the code module for the sheet. Right click on the sheet tab and select view code. Then paste the code there.

Regards, TMS

[jmac1947]

@andy, small issue, the second last line of your suggested code should be "= True" perhaps (having just fallen into the same issue myself on another project)

[AndyLitch]

Thanks for that JMac (damn this cut and paste LOL)

Lloyd ... Recopy the code again ..... I accidentally disabled event trapping....

[TMS]

@LLOYD6664: has your question been answered or do you need further help with this?



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[LLOYD6664]

hI
Dont know what i am doing wrong cut and paste into the worksheet code still cant get it to run
i haave various coloumns when i enter Y into column b i want it to put date into column c and time into coumn d
when i put Y into column E i want it to put time into column f etc. i have attached the sample file

Can some eone please help im pulling my hair out here and i havent got that much left

[nickmax1]

lloyd6664
the code says that if you put something in a cell in colomn A, the date is placed in Col B and time in Col C as you requested...

[nickmax1]

DBL post.....deleted.

[TMS]

The code in the sample workbook provided works fine. What are your macro security settings? Your profile says 2003 but this is a 2007+ workbook. Have you enabled macros?


Regards, TMS

[Flee0723]

Ive used this a function before by doing this and dragging the formatting down the 50 rows...
For cells B =IF(A<>"",IF(B="",NOW(),B),"")
and for Cells C =IF(A<>"",IF(C="",NOW(),C),"")
Just be sure to format the cells with the date and time formats you want

[LLOYD6664]

sorry nick

forgot to add a couple of columns sstill cant get it working been trying for the last two hours is it possible to look at the
spreaddsheet i attached and attach the code so i can see where i am going wrong.

[AndyLitch]

Book9.xlsm

I would like to enter data in cell a and i would like cell b to automatically enter the date and cell c enter the time.

Because the code was written to respond in changes to column A as initially specified... It therefore only worked on changes to column A but the actual requirement was to respond to column B changes.

Another example of why uploading the workbook in the first instance is a good idea

Can you mark this as eventually solved please

[LLOYD6664]

Sorry andy for the confusion i have downloaded the book9 and still doesnt do anything
is there spmething on my machine which is preventing this do you know of anything ?

[AndyLitch]

Too many book 9's maybe...also make sure macro's are enabled....

XYZ.xlsm

[LLOYD6664]

Thanks andy

I really appreceiate your help i have downloaded the xyz file and tried to enter a y into the b column and nothing
happened the date /time didnt seem to do anything i closed the file reopened it and nothing.
this must be something to do with one of the settings i have no other files with this name can you suggest anyhting else
that may prevent this working. ?

[TMS]

What version of Excel are you using? Have you enabled macros in your security settings? When you open the workbook, does it ask you if you want enable macros? If it does, do you click OK?

Regards, TMS

[AndyLitch]

Open this one and click the test button it should display a message and then fill in 10 rows of dates and times


XYZ.xlsm

[LLOYD6664]

What version of Excel are you using? Have you enabled macros in your security settings? When you open the workbook, does it ask you if you want enable macros? If it does, do you click OK?

Regards, TMS

I am running office 2007 i have enabled macros under the developer tab ?

[Flee0723]

Book9autofill.xlsm
Try this LLOYD

[LLOYD6664]

Thank-you flee0723

this works fine the only thing i need it do now is in column b if the contents are anything other than a "Y" it does not
fill in the date and time when coloumn e contains a number between 1-84 it puts the time in F and lastly when a "Y" is put in column
g that it puts the time in coumn h. can this be done in thee same code or do you need to have sheets as you can guess i am a complete new starter at the vba side of excel.