Need help with VBA for today() and now() functions

[MSant14]

I need a macro code inserted into my workbook that will stop the Today() and now() functions from recalculating every time a change is made in the work book. Students will be typing in numbers in columns A and E. When their number is inserted in column A, the "name"(column B), "date"(column C) and "time out"(column D) will populate. When they put their number in column E, the "time in"(column F) will populate. I need a specific macro code that will prevent these formulas from recalculating once they are inserted. Thanks so much!

 

[Joe4]

Welcome to the Board!

The TODAY() and NOW() functions will always return the current date/time. There is no way to tell them to return anything different or "freeze it". You would actually need to copy over their value to a hard-coded value to "freeze" it at a specified point in time.

It sounds like what you actually want is a date/time stamp. Here are some links that show you how to do that.
https://www.mrexcel.com/forum/excel-questions/706972-vba-code-autopost-date-time-stamp.html
https://www.mrexcel.com/forum/excel-questions/1024261-static-date-time-stamp-help-vba.html

Post back if you run into any issues applying it.

 

[AlphaFrog]

This macro will stamp the date and time in columns C and D when the user enters a value on column A. It also stamps the time in column F when the user makes an entry in column E.

To install the code:

• Right-click on the sheet Tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Value <> [COLOR=darkblue]Empty[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Column = 1 [COLOR=darkblue]Then[/COLOR]
                Range("C" & Target.Row).Value = [COLOR=darkblue]Date[/COLOR]
                Range("D" & Target.Row).Value = Time
            [COLOR=darkblue]ElseIf[/COLOR] Target.Column = 5 [COLOR=darkblue]Then[/COLOR]
                Range("F" & Target.Row).Value = Time
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[My Aswer Is This]

This is a auto sheet event script.
When user enters a value in column "A" todays date will be entered in Column (C)
And Time now will be entered in column (D)

And if they enter a value in Column(E) Time now will be entered in column (F)

Now you said name gets automatically entered in column (B)
But how does script know what name is associated with number entered in Column (A)

These dates and times will not change

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-10-18 10:25 AM EST
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 1 Then: Target.Offset(0, 2).Value = Date: Target.Offset(0, 3).Value = Time
If Target.Column = 5 Then Target.Offset(0, 1).Value = Time
End Sub

 

[MSant14]

So when I paste this code into my work tool and insert a value in column A, I get an error that says "Compile error: Expected function or variable". Then it pulls up my code and highlights the word "Time" that you have at the end of the 6th row of the code. Do you know why that would be? I didn't mention that column B has a vlookup that pulls in a name from another tab depending on what number is inserted in column A. Not sure if that has anything to do with it. Thanks

 

[My Aswer Is This]

Try using my script on a empty worksheet and see what happens.
What version of Excel are you using?
Are you using a PC or a "Apple" computer?

 

[MSant14]

It works! I didn't realize I had to clear the original formulas I had from the date and time columns. Thanks for your help! I do have one more quick question that just came up though. I want to protect all of the columns except the 2 number columns. I noticed that the macro doesn't work if I protect everything. Is there anything I can add to the code that will allow me to protect the sheet? Thanks again for your help

 

[My Aswer Is This]

I'm not familiar with Protecting and Unprotecting sheets or ranges in a sheet.
I believe there are about 400 different options. Can do this cannot do this.

I suggest you make another posting and ask this question.

 

[AlphaFrog]

It works! I didn't realize I had to clear the original formulas I had from the date and time columns. Thanks for your help! I do have one more quick question that just came up though. I want to protect all of the columns except the 2 number columns. I noticed that the macro doesn't work if I protect everything. Is there anything I can add to the code that will allow me to protect the sheet? Thanks again for your help

Change the password to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Value <> Empty Then
            [B]ActiveSheet.Unprotect Password:="Secret"[/B]
            If Target.Column = 1 Then
                Range("C" & Target.Row).Value = Date
                Range("D" & Target.Row).Value = Time
            ElseIf Target.Column = 5 Then
                Range("F" & Target.Row).Value = Time
            End If
            [B]ActiveSheet.Protect Password:="Secret"[/B]
        End If
    End If
End Sub