Forum Discussion

pk225's avatar
pk225
Copper Contributor
Feb 09, 2023

Producing Date based on Drop Down

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

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 10, 2023

    pk225 

    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.

Resources