Help with permanent dates into 2 different cells based on validated list

[Ozads]

Hi,

firstly, thanks for everyone's posts in general in this forum, many have been really helpful for me, especially with me learning some VBA.

My question today is, for my technician job worksheet below, I would like to automatically enter the current date when a job is started (L3) and then the current date when a job is finished (P3). Both processes being based on selecting the correct value in column H3 (validated list). So, Start Date needs to populate automatically when I change the status to "IN PROGRESS" and, once the job has been made, the Delivery Date needs to populate automatically when I change the status to "COMPLETED", without changing the Start Date that was previously entered.

I can get the current date to populate when either one is selected, but can't get either a formula or VBA to hold a date that was previously entered automatically (I don't want it to change once entered). It's important for me to keep a record of how long our jobs are taking from start to finish.

If someone could help me with this I'd be really grateful. Happy to run it as VBA or as a formula.

Thanks in advance,

Adam

 

[Micron]

Not sure what you've got there. Are those coloured things buttons? You are storing the status in H3? How is it getting in there? As for VBA, perhaps could use Worksheet_Change event and make changes accordingly. Another route might have been to use a combobox with a list of your status choices to do the same. But then you wouldn't have all that eye candy on there!

 

[Ozads]

Sorry, maybe I wasn't being clear.

Cells in column H are a validated list (the one with the colours).

What I basically want is to have a macro that automatically does the following for me:
When column H = "IN PROGRESS" put date in column L
When column H = "COMPLETED" put date in column P (without losing or changing the date already in column L)

I would like it to work like this:
1. Enter a new job with a status of Waiting (user will manually enter received and required dates)

2. When a job gets started, the user will change the status to 'IN PROGRESS' and the start date gets entered automatically as the current date

3. When a job is finished, the user will change the status to 'COMPLETED' and the job completed date gets entered automatically as the current date

Thanks,

Adam

 

[My Aswer Is This]

So if I understand correctly when a user enters "Completed" in column H script will enter Todays Date in column P. Is that all and correct?

 

[Ozads]

OK, so I think I've got it. I've used the following code placed against the required worksheet and it seems to do what I need.



Private Sub Worksheet_Change(ByVal Target As Range)

' Auto_enter_start_completed_date Macro

Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("H:H").Column Then
If Cell.Value = "IN PROGRESS" Then
Cells(Cell.Row, "L").Value = Date
End If


If Cell.Value = "COMPLETED" Then
Cells(Cell.Row, "P").Value = Date

End If
End If
Next Cell
End Sub


Is there anything I've missed or could this be done better than I have?

Thanks,

Adam

 

[Micron]

Maybe this will get you started.

Private Sub ComboBox2_Change()

Select Case ComboBox2
  Case "IN PROGRESS"
    Range("L3") = Date

  Case "COMPLETED"
    Range("P3") = Date

End Select
End Sub

Your answer came up right after I pasted but I'll leave it here.
Please use code tags when posting code, like I did here (vba button on posting toolbar).

 

[My Aswer Is This]

I would do it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/20/2022  8:52:23 PM  EDT
If Target.Column = 8 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "IN PROGRESS" Then Target.Offset(, 4).Value = Date
If Target.Value = "COMPLETED" Then Target.Offset(, 8).Value = Date

End If
End Sub

 

[My Aswer Is This]

If you want to use Case. Try this:

Case is used instead of a lot of if statements.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/20/2022  9:03:23 PM  EDT
If Target.Column = 8 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

Select Case Target.Value
    Case "IN PROGRESS": Target.Offset(, 4).Value = Date
    Case "COMPLETED": Target.Offset(, 8).Value = Date
End Select

End If
End Sub

 

[My Aswer Is This]

Here is another example using case. If you're interested in learning more.
Try entering any value in column "H" and see what happens.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  4/20/2022  9:12:21 PM  EDT
If Target.Column = 8 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

Select Case Target.Value
    Case "IN PROGRESS"
        Target.Offset(, 4).Value = Date
        MsgBox "Hello"
    
    Case "COMPLETED"
        Target.Offset(, 8).Value = Date
        MsgBox "Goodbye"

    Case Else
        MsgBox "You entered " & Target.Value & vbNewLine & " That is not a value we run on"

End Select

End If
End Sub

 

[Ozads]

Hi, thank you so much for your replies and apologies for the code not tagged correctly.

I like your code options way better than mine. I'm using the case statement option.

Thanks also for the extra option with the message boxes. That's made me think further and I have some uses for this so will be able to apply it too.

Cheers,

Adam