software

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 = quot;C4quot;

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) = quot;sheetquot; amp; iCtr Then
wks.Name = Format(Target.Value (7 * iCtr), quot;dd mmm YYquot;)
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

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:
gt;
gt; Hello Folks
gt; I have a workbook with 53 worksheets. the first 52 sheets have the tab name
gt; as the week-ending date. The 53rd sheet is called “Info” and contains the
gt; following code. (I am very grateful to Dave Peterson for providing me with
gt; this code.)
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Const WS_RANGE As String = quot;C4quot;
gt;
gt; Dim iCtr As Long
gt; Dim wks As Worksheet
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; For Each wks In Me.Parent.Worksheets
gt; For iCtr = 1 To 52
gt; If LCase(wks.CodeName) = quot;sheetquot; amp; iCtr Then
gt; wks.Name = Format(Target.Value (7 * iCtr), quot;dd mmm YYquot;)
gt; Exit For
gt; End If
gt; Next iCtr
gt; Next wks
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; In Info!C4, I applied a data validation with the custom formula of
gt; =weekday(C4)=1 and a warning that if the date input was not a Sunday, along
gt; with the question “Are you absolutely sure you wish to go ahead?”
gt; The problem starts here.
gt; If I input in C4 a date that is not a Sunday, I get the question “Are you
gt; absolutely sure” press yes and the tab name changes. When I then input a date
gt; that is a Sunday, the tab name changes as normal.
gt; If however I have in the cell a Sunday already, eg 1/1/06 and want to change
gt; it to 8/1/06 then I do not get the tab name change. Please can someone tell
gt; me why?
gt; (using Excel XP)
gt; I hope you can understand this logic.
gt;
gt; Your help is and always has been very much appreciated.
gt; Thanking you in anticipation.
gt; --
gt; Big Rick

--

Dave Peterson

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 Rickquot;Dave Petersonquot; wrote:

gt; I couldn't duplicate this.
gt;
gt; But if you've been coding/debugging, maybe you stopped the code and left
gt; ..enableevents = false.
gt;
gt; I'd try this:
gt; alt-f11 to get to the VBE
gt; ctrl-g to see the immediate window
gt;
gt; type this and hit enter:
gt; application.enableevents = true
gt;
gt; Then back to excel to test it out.
gt;
gt; Big Rick wrote:
gt; gt;
gt; gt; Hello Folks
gt; gt; I have a workbook with 53 worksheets. the first 52 sheets have the tab name
gt; gt; as the week-ending date. The 53rd sheet is called “Info” and contains the
gt; gt; following code. (I am very grateful to Dave Peterson for providing me with
gt; gt; this code.)
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Const WS_RANGE As String = quot;C4quot;
gt; gt;
gt; gt; Dim iCtr As Long
gt; gt; Dim wks As Worksheet
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; For Each wks In Me.Parent.Worksheets
gt; gt; For iCtr = 1 To 52
gt; gt; If LCase(wks.CodeName) = quot;sheetquot; amp; iCtr Then
gt; gt; wks.Name = Format(Target.Value (7 * iCtr), quot;dd mmm YYquot;)
gt; gt; Exit For
gt; gt; End If
gt; gt; Next iCtr
gt; gt; Next wks
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; In Info!C4, I applied a data validation with the custom formula of
gt; gt; =weekday(C4)=1 and a warning that if the date input was not a Sunday, along
gt; gt; with the question “Are you absolutely sure you wish to go ahead?”
gt; gt; The problem starts here.
gt; gt; If I input in C4 a date that is not a Sunday, I get the question “Are you
gt; gt; absolutely sure” press yes and the tab name changes. When I then input a date
gt; gt; that is a Sunday, the tab name changes as normal.
gt; gt; If however I have in the cell a Sunday already, eg 1/1/06 and want to change
gt; gt; it to 8/1/06 then I do not get the tab name change. Please can someone tell
gt; gt; me why?
gt; gt; (using Excel XP)
gt; gt; I hope you can understand this logic.
gt; gt;
gt; gt; Your help is and always has been very much appreciated.
gt; gt; Thanking you in anticipation.
gt; gt; --
gt; gt; Big Rick
gt;
gt; --
gt;
gt; Dave Peterson
gt;