Automatic Date and Time

jblair626

New Member
Joined
Oct 17, 2011
Messages
18
I want the date and time to automatically populate when I enter something into cell C5
I want the Date to auto update in C6
and the time to auto update in C7 as soon as i enter something into C5
Is there a formula for this?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

You can use a Change event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C5")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Target<br>                .Offset(1).Value = <SPAN style="color:#00007F">Date</SPAN><br>                .Offset(2).Value = Time<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,
 

jblair626

New Member
Joined
Oct 17, 2011
Messages
18
I tried to copy and paste that to the work sheet after clicking veiw code. But when I enter values in cell c5, c6 and c7 do not change and a pop up appears that says end or debug I've tried both but cnnot figure it out.
Please help
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
OK, so you copied it to the worksheet module, which is the right thing to do.

What error message do you get and what line of code is highlighted?

You can also try Debug->Compile from the VBE to check for the error. It may be as simple as a missing reference.
 

jblair626

New Member
Joined
Oct 17, 2011
Messages
18

ADVERTISEMENT

It wasnt working because the sheet was protected and those 2 cells were locked. I unlocked them and now it works. Thank you
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Glad you got it sorted. If you don't want users to be able to edit C6:C7, then you can uprotect/protect the sheet programmatically:

ActiveSheet.Unprotect "Password"
' Code
ActiveSheet.Protect "Password"
 

jblair626

New Member
Joined
Oct 17, 2011
Messages
18
But when I close it and save it and open it back up. Everything on the code page goes away. How do I keep it there. Ive clicked the save icon multiple times.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Try saving the workbook from Excel instead of the VBE.

If you're in Excel 2007+ you'll need to save it as a Macro Enabled workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,935
Messages
5,525,697
Members
409,660
Latest member
1817538628

This Week's Hot Topics

Top