Tabname Changes

[Guest]

Hello Folks
I have a workbook with 53 worksheets. the first 52 sheets have the tab name
as the week-ending date. The 53rd sheet is called “Info†and contains the
following code. (I am very grateful to Dave Peterson for providing me with
this code.)

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C4"

Dim iCtr As Long
Dim wks As Worksheet

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each wks In Me.Parent.Worksheets
For iCtr = 1 To 52
If LCase(wks.CodeName) = "sheet" & iCtr Then
wks.Name = Format(Target.Value + (7 * iCtr), "dd mmm YY")
Exit For
End If
Next iCtr
Next wks
End If

ws_exit:
Application.EnableEvents = True
End Sub

In Info!C4, I applied a data validation with the custom formula of
=weekday(C4)=1 and a warning that if the date input was not a Sunday, along
with the question “Are you absolutely sure you wish to go ahead?â€
The problem starts here.
If I input in C4 a date that is not a Sunday, I get the question “Are you
absolutely sure†press yes and the tab name changes. When I then input a date
that is a Sunday, the tab name changes as normal.
If however I have in the cell a Sunday already, eg 1/1/06 and want to change
it to 8/1/06 then I do not get the tab name change. Please can someone tell
me why?
(using Excel XP)
I hope you can understand this logic.


Your help is and always has been very much appreciated.
Thanking you in anticipation.

 

[Dave Peterson]

I couldn't duplicate this.

But if you've been coding/debugging, maybe you stopped the code and left
..enableevents = false.

I'd try this:
alt-f11 to get to the VBE
ctrl-g to see the immediate window

type this and hit enter:
application.enableevents = true

Then back to excel to test it out.

 

[Guest]

Thanks for replying Dave.
It didn't work i'm afraid.
As this is only something that is changed once a year, I guess it will not
be too much of a hardship to go the long way round.

Thanks you again. I really do appreciate all your time and effort.