Tabname Changes

[Big Rick]

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.
--
Big Rick

[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.

Big Rick wrote:
>
> 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.
> --
> Big Rick

--

Dave Peterson

[Big Rick]

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.
--
Big Rick


"Dave Peterson" wrote:

> 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.
>
> Big Rick wrote:
> >
> > 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.
> > --
> > Big Rick
>
> --
>
> Dave Peterson
>