timestamp whem cell changes by formula

[peterniesten]

Hi,

I'm trying to make a timestamp in B2 when cell A1 is updated by a formula.

Thanks

 

[AlphaFrog]

What's the formula in A1?
What cells does the user change that affect the A1 formula?

 

[peterniesten]

This is the formula i'm using:

=ALS(ISFOUT(=VERT.ZOEKEN($L$16;E2:H2;4;0));" ";=VERT.ZOEKEN($L$16;E2:H2;4;0))

I'll explain the purpose for you:
I'm a teacher and when the students hand in there exames, we have to (by law) write down the time they hand it over. So i'm trying to make it easier by having al the studends numbers in an excel sheet. When i enter there studentnumber, something is written down in A1. I hope to automaticly get a time stamp there.

Sorry for my bad English... I'm from the Netherlands

 

[AlphaFrog]

So a macro can be made where if you enter a student number in say column B, then the macro will automatically put a time stamp in column A. The macro actually time stamps your student number entry and not the formula calculating.

To install the macro:

• 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
• Change the B:B to the column of cells you change (not the formula column).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("[B]B:B[/B]")) Is Nothing Then
            If Target.Value <> "" Then Target.Offset(0, -1).Value = Now
        End If
    End If
End Sub

 

[SpillerBD]

You might add an Else statement to BEEP or have a message box to point out there was already a value in "A"

 

[peterniesten]

Thanks, for your answer, it works, but when I enter the studens number, I always enter it in Cell L16, then vertical search finds the right student. So it is important that the timestamp displays when the cell with the formula is updated.

 

[AlphaFrog]

Thanks, for your answer, it works, but when I enter the studens number, I always enter it in Cell L16, then vertical search finds the right student. So it is important that the timestamp displays when the cell with the formula is updated.

Both the formula and the macro code simultaneously update when you enter a value.

Do you enter student numbers in column L and have formulas in column B and want a time-stamp in column A?

 

[peterniesten]

Yes like that, I only changed the sheet a little sinds this morning. The formula is in column F, I want the timestamp in Column G, and i enter the studentsnumers in cell L1.

Really hope you can help me!

 

[peterniesten]

Found it:

Private Sub Worksheet_Calculate()
Dim r As Range
For Each r In Intersect(Range("F:F"), ActiveSheet.UsedRange)
If r.Value = 1 And r.Offset(0, 1).Value = "" Then
Application.EnableEvents = False
r.Offset(0, 1) = Time
Application.EnableEvents = True
End If
Next r
End Sub


Thanks for your help!