Excel 2007 : Changing the month

[sushil10s]

Hi

Attached is the file and wish to know (may be something) that if date 31 crosses the freezed line month shall automatically change to the next one, i.e, currently its for december but when 31 crosses freezed pane/line it should get automatically changed to January.

Thanks

[protonLeah]

The macro below will change the month and move the active cell to the first day of the month. For it to work you must tab or click in the white margin at the end of the calendar.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("F3:AK23")) Is Nothing Then Exit Sub
    Dim CurDate As Date, _
        Mon As String, _
        Yr As String
    
    CurDate = Range("B8").Value
    
    On Error Resume Next
    With ActiveWindow
        .ScrollColumn = ActiveCell.Column
    End With
    If Cells(2, ActiveCell.Column).Value = "" Then
        Mon = (Month(CurDate) + 1) Mod 12
        Yr = IIf(Mon = 1, Year(CurDate) + 1, Year(CurDate))
        Range("B8").Value = Format(Mon & "/01/" & Yr, "mmmm-yyyy")
        Range("F3").Activate
    End If
    On Error GoTo 0
End Sub

The last three day number cells in the calendar header contain formulas to determine if days past 28 are displayed for Feb & 30 day months:
AH2: =IF(AG2+1>DAY(EOMONTH($B$8,0)),"",AG2+1)
AI2: =IF(AH2="","",IF(AH2+1>DAY(EOMONTH($B$8,0)),"",AH2+1))
AJ2: =IF(AI2="","",IF(AI2+1>DAY(EOMONTH($B$8,0)),"",AI2+1))