Automatic Date and Time

[jblair626]

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?

 

[Smitty]

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]

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]

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]

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

 

[Smitty]

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]

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]

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.