How to display a rolling 12 month period within a defined range

[Aussiebear]

I am looking for a method to be able to change the order of display of the names 12 months (Range A17:A28) by the user selecting a new value in a reference cell A15



Sub AdjustMonthRange()
Dim StartMonth As Integer
Dim StartCell As Range
Dim i As Integer '
Prompt the user for the start month (1 for January, 2 for February, etc.)
StartMonth = InputBox("Enter the starting month (1-12):")
'Specify the starting cell of the month range
Set StartCell = Range("A17")
' Adjust this to your desired starting cell
‘Loop through the cells and populate with month names, adjusting for the start month
For i = 1 To 12
StartCell.Offset(i - 1, 0).Value = MonthName((StartMonth + i - 1) Mod 12 + 1)
Next i
End Sub


This however requires the User to select a number (1-12) rather than a Month name.

I'm not confident that this version is any better



Sub DisplayNext12Months()
Dim startMonth As Integer
Dim i As Integer
Dim cell As Range
' Get the starting month number from cell A15
startMonth = Month(Range("A15").Value)
' Set the starting cell for the month names
Set cell = Range("A17")
' Loop through the next 12 months
For i = 1 To 12
' Calculate the month number, ensuring it wraps around to 1 after 12
Dim monthNumber As Integer = (startMonth + i - 1) Mod 12 + 1
' Use the MonthName function to get the month name
cell.Value = MonthName(monthNumber)
' Move to the next cell
Set cell = cell.Offset(1, 0)
Next i
End Sub


Does anyone have any suggestions?

[p45cal]

Set up a data validation list somewhere type Jan or January in a cell and drag down (custom lists will handle that). Use that list as data validation in cell A15.
Have the following code in that sheet's code-module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A15"), Target) Is Nothing Then
Application.EnableEvents = False
Range("A17:A28").Value = Evaluate("TEXT(EDATE(DATE(1,12,1),SEQUENCE(12,,MONTH(DATEVALUE(A15 & ""-1"")))),""mmmm"")")
Application.EnableEvents = True
End If
End Sub
The above is in the attached and a formula version of the same at cell C17.

[Aussiebear]

Excellent. Thank you P45cal.