Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatically Timestamping based off the Text of Another Cell

0

Hey so I am still a beginner at VBA, and I recently watched the video on Automatically Timestamping Data Entries in Excel. Though what I am looking for is a little different.

To describe: I am trying to build a production schedule where in column J there is a dropdown list with the options of: Not Started, Start, Pause, Finish. I am looking to write some VBA code so that when an operator selects the "Start" option from the dropdown list, it would automatically populate column K with a timestamp. And then in column L, it would autopopulate with a timestamp when the operator selects the "Finish" option from the list. Can anyone assist me with this?

Answer
Discuss

Answers

0
Selected Answer

 I haven't seen that video but in the attached file I've applied data validation (from cells E6:E10 but yours will be elsewhere) to the cells in J2 :J13 only.

I've written a short macro (with comments) which is triggered whenever a change is made to the worksheet (if you look in thr VBA Explorer, you'll see the box top right shows "Change").

It checks (using Intersect) if the change was made to as cell ("Target") in that J2:J13 range and if so uses nested If statements to A) do nothing if more than one cell is elected B) add the time/date to L if Finish was the changed value in a single cell or C) the same to K so other selections. Those chnage use the Offset (be rows and columns) from the Target and the Now fucntion for the current time/date (but you could use Date if you don't need the time bit).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub 'Do nothing if more than one cell is changed
If Intersect(Target, Range("$J$2:$J$14")) Is Nothing Then 'Check if they changed something in column J
                                                        'if not do nothing
   Else       
        If Target.Value = "Finish" Then ' When the operator selects Finish..
            Target.Offset(0, 2).Value = Now ' Put time in cell 2 to right, i.e. K
        Else
        Target.Offset(0, 1).Value = Now 'Otherwise write time in L   
    End If  
End If
End Sub

Hope this helps

Discuss

Discussion

This helps immensely, thank you very much.

Like I had mentioned, I'm still a beginner, but this is exactly what I was looking for. It seems like the offset function is very helpful, that's something for me to learn more about.

I guess a follow up question is, should I be declaring the "Finished" variable as a string?

But thanks again for the tremendous help.
JRhoda (rep: 2) Oct 27, '20 at 2:50 pm
There's no need to declare that as a variable but instead of comparing the changed value to the string "Finished" as I did, it would be better to compare the the value of the reference cell for your data validation (wherever that is) just in case you decide to change that status to "Completed" say (which would presently mean column L would not be populated. 

Any way I believe I've answer your original question so please Select my answer as being correct. Thanks 

John_Ru (rep: 6142) Oct 27, '20 at 5:07 pm
@JRhoda If the answer solved your problem please do mark it as "Selected". Thank you.
Variatus (rep: 4889) Oct 27, '20 at 9:30 pm
@Variatus - thanks for advising the correct procedure. 

@JRhoda - if this solution works, please follow the advice above 
John_Ru (rep: 6142) Oct 29, '20 at 5:46 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login