VBA Dropdown list of month values dependent on current month

[bgrice]

Hi. I'd like to create a dropdown list of month and year values that is dependent on the current month. There should be six values and ideally the first value would be three months on from the current date. For example, if the dropdown is clicked today (Jan-22), then the values from the dropdown will be Apr-22, May-22, Jun-22, Jul-22, Aug-22 and Sep-22. If the dropdown is clicked in Aug-22 then the values will be Nov-22, Dec-22, Jan-23, Feb-23, Mar-23 and Apr-23.
I'd be grateful for any thoughts on how this could be achieved. Thank you.

 

[JGordon11]

You don't include your excel version, so this may not work for you. If you have 365 this will work:

Book1
	A	B
1	Apr-22
2	May-22
3	Jun-22
4	Jul-22
5	Aug-22
6	Sep-22
7
Sheet1

Cell Formulas
Range	Formula
A1:A6	A1	=TEXT(LET(d,TODAY(),DATE(YEAR(d),MONTH(d)+SEQUENCE(6,1,3),DAY(d))),"mmm-yy")
Dynamic array formulas.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to the cell that will contain the drop down list. Close the code window to return to your sheet. Click on the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim Choices As String, x As Long
    For x = 1 To 6
        If Choices = "" Then
            Choices = Format(WorksheetFunction.EDate(Date, x + 2), "Mmm-yy")
        Else
            Choices = Choices & "," & Format(WorksheetFunction.EDate(Date, x + 2), "Mmm-yy")
        End If
    Next x
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Choices
    End With
End Sub

 

[bgrice]

Mumps, Thank you so much for the speedy reply. It absolutely nailed the brief.
Fantastic community. Thanks to all that replied.

 

[mumps]

You are very welcome.

 

[bgrice]

Mumps, Sorry, I have a follow-up question on your code if you wouldn't mind answering, please.

The cell displays as mmm-yy but the value when selected, and shown in the formula bar, is always the 22nd of the month. The date is being used to drive other calculations in my worksheet and the value always needs to be the 1st of the month.

How could I amend the code to either (i) still display as mmm-yy but have the value always be the first of the month or, if easier, (ii) display and value as first of the month?

Thank you.

 

[mumps]

Does this work for you?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim Choices As String, x As Long
    For x = 1 To 6
        If Choices = "" Then
            Choices = Format(WorksheetFunction.EDate(Date, x + 2), "Mmm-01-yy")
        Else
            Choices = Choices & "," & Format(WorksheetFunction.EDate(Date, x + 2), "Mmm-01-yy")
        End If
    Next x
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Choices
    End With
End Sub

 

[bgrice]

Thank you, Mumps. That does work. However, I am based in the UK so we write the date as day - month - year. (Sorry, I am new to this forum and I see now that points of detail like this are important and I must ensure I include in any initial posting in future.)

Before my last post, I did try a solution similar to yours above but in the UK date format: 01-mmm-yy. But this returns something that is I think is a serial number but is meaningless to the layman - 01 Apr 22 appears as 446671-mmm-yy.

Any ideas what I am doing wrong? Thanks for your continued help.

 

[mumps]

Working with dates is always very tricky. How about:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim Choices As String, x As Long
    For x = 1 To 6
        If Choices = "" Then
            Choices = Day(Date) & "-" & Month(Date) + x + 2 & "-" & Year(Date)
        Else
            Choices = Choices & "," & Day(Date) & "-" & Month(Date) + x + 2 & "-" & Year(Date)
        End If
    Next x
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Choices
    End With
End Sub

 

[bgrice]

Thank you, Mumps. That worked like a treat. I just replaced 'Day(Date)' with "01" to give the first of the month. A little out of my skillset yet but I must learn to use arrays. Much appreciated.