I can't get private sub to run to update sheet name when the value in Cell A1 has changed

[An Excel User]

Hi, I am a VBA newbie. I have a macro that runs to update the first Monday of the the year, when the year is entered ( Monday dates). This is then used to populate an Excel list of every Monday in that year. It then locks the sheet so the dates can't be deleted.
These dates are linked to Cell A1 in in a Worksheet for each week of the year. I want this updated date in A1 to become the new sheet name.
I have a Private sub Worksheet_change (listed below) in each Worksheet, I can't seem to get this to work to actually make a change to the sheet name, even though the value in cell A1 has changed. What am I doing wrong?

Sub MONDAY_DATES()

Dim DAY_NO As Integer
Dim inputyear As Integer

Application.ScreenUpdating = False
Sheet18.Select 'parameters
ActiveSheet.Unprotect

On Error Resume Next
inputyear = Application.InputBox("Please enter the new year - format: YYYY")

If inputyear = False Or 0 Then
Do Until inputyear <> False Or 0
MsgBox ("please enter a year in the valid format!")
inputyear = Application.InputBox("Please enter the new year - format: YYYY")
Loop
End If
NEWYEAR = "01/01/" & inputyear
Range("A2").Value = NEWYEAR


Range("b2").Select
NEWYEAR = Range("A2").Value
ActiveCell.FormulaR1C1 = "=WEEKDAY(R2C1,2)"
DAY_NO = ActiveCell.Value

Select Case DAY_NO
Case 7
StartDate = NEWYEAR - (DAY_NO - 1)
Case 6
StartDate = NEWYEAR - (DAY_NO - 1)
Case 5
StartDate = NEWYEAR - (DAY_NO - 1)
Case 4
StartDate = NEWYEAR - (DAY_NO - 1)
Case 3
StartDate = NEWYEAR - (DAY_NO - 1)
Case 2
StartDate = NEWYEAR - (DAY_NO - 1)
Case Else
StartDate = NEWYEAR
End Select

ActiveCell.Value = StartDate

Range("A3:A18").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
On Error Resume Next
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("A1")
ElseIf Not Intersect(Target.Dependents, Range("A1")) Then
ActiveSheet.Name = ActiveSheet.Range("A1")
End If
End Sub

 

[duggie33]

Hello,

You could use a formula to get a list of Mondays for any year:

=LET(year_dates,DATE(2023,1,SEQUENCE(365)),year_days,WEEKDAY(year_dates),FILTER(year_dates,year_days=2))

To work through VBA question would require more time than I have at the moment.

Hope this helps,

Doug