Feb 09 2023 11:48 AM
I am working to automate a "Project Start Date" field based on a drop down menu. For example, the dropdown menu has three option "Not-started", "Started", and "Completed". Once "Started" is selected in the dropdown menu, I want my "Project Start Date" column to produce the date at that time. I would then like that date to remain indefinitely. Any suggestions on how to accomplish this? Thanks
Feb 10 2023 05:07 AM
Let's say you have Project Status drop-downs in D2:D100, and Project Start Date is column F.
Right-click the sheet tab.
Copy the following code into the worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
On Error GoTo ErrHandler
If Not Intersect(Range("D2:D100"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cel In Intersect(Range("D2:D100"), Target)
If cel.Value = "Started" Then
Range("F" & cel.Row).Value = Date ' or Now if you want the time too
ElseIf cel.Value = "" Then
Range("F" & cel.Row).ClearContents
End If
Next cel
End If
ExitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Switch back to Excle.
Save the workbook as a macro-enabled workbook *.xlsm).
Make sure that you allow macros when you open it.