adit123
adit123

Reputation: 117

Applying a formula depending on selection from a dropdown using VBA

I am trying to develop a VBA code in order to apply a specific formula based on a selection from a dropdown list I created.

So I have created the list with a dropdown list: Annually, Bi-Annually, Semi-Annually, and Quarterly. Then in the next column is the "Last Revision Date" that was completed. Based on what someone chooses from my list. I want the "Last Revision Date" to correlate with my selection.

For example, if the "Last Revision Date" is May-2014 and the selection from the dropdown list is "Annually". I want the next column, "Next Revision Date" to be automatically filled with May-2015 since I chose Annually.

How would i go about this code?

Just take column "A" to have the frequency(Annually, Bi-Annually, Semi-Annually, and Quarterly). Take column "B" to have the last date. Take column "C" to have the next date depending on the selection from column "A".

Thanks!

Upvotes: 0

Views: 1981

Answers (1)

Dan
Dan

Reputation: 419

If you are wanting to keep this out of formulas and store it in code, I believe the below should solve your issue.

Worksheet_Change function must be in the same sheet as the drop down to be able to trigger the macro

The IsDate function below will make sure there aren't errors if the users doesn't have a date in the 2nd column.

The DateAdd function has multiple inputs, more info here.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim thisSheet As Worksheet
Set thisSheet = Worksheets("Answer")

Dim nextDateRng As Range
Set nextDateRng = thisSheet.Cells(Target.Row, 3)

Dim dateRng As Range
Set dateRng = thisSheet.Cells(Target.Row, 2)

' makes sure the first column was change
If Not Target.Column <> 1 Then

    ' annually selection moves date 12 months
    If Target.Value = "Annually" And IsDate(dateRng.Value) Then
        monthsToAdd = 12
        nextDateRng.Value = DateAdd("m", monthsToAdd, dateRng.Value)
    End If

    ' bi-annually selection moves date 12 months
    If Target.Value = "Bi-Annually" And IsDate(dateRng.Value) Then
        monthsToAdd = 24
        nextDateRng.Value = DateAdd("m", monthsToAdd, dateRng.Value)
    End If

    ' semi-annually selection moves date 12 months
    If Target.Value = "Semi-Annually" And IsDate(dateRng.Value) Then
        monthsToAdd = 6
        nextDateRng.Value = DateAdd("m", monthsToAdd, dateRng.Value)
    End If

    ' quarterly selection moves date 12 months
    If Target.Value = "Quarterly" And IsDate(dateRng.Value) Then
        monthsToAdd = 3
        nextDateRng.Value = DateAdd("m", monthsToAdd, dateRng.Value)
    End If

End If

End Sub

Upvotes: 1

Related Questions