Hello to all my good friends out there.

I have the following code in my worksheet.

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Which will alter the tab name of the worksheet with whatever is in cell J3.
If however the contents of j3 changes, the tab name doesn't change. Not when
I press f9, even though calculation is set to auto and doesn't even change
when saving and reopening the workbook. The only way it does change is when I
click on the cell itself and press enter again.
Is there a way to have the tab name change immediately after the cell change.

eg. j2 = 5/3/1999
j3 = =TEXT(J2,"dd mm")

When j2 changes the tab name doesn't change until I click on J3 and press
enter.

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

Regards
--
Big Rick

Worksheet_change waits for you to type something. (Your code checks to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you wanted.

Big Rick wrote:

Hello to all my good friends out there.

I have the following code in my worksheet.

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Which will alter the tab name of the worksheet with whatever is in cell J3.
If however the contents of j3 changes, the tab name doesn't change. Not when
I press f9, even though calculation is set to auto and doesn't even change
when saving and reopening the workbook. The only way it does change is when I
click on the cell itself and press enter again.
Is there a way to have the tab name change immediately after the cell change.

eg. j2 = 5/3/1999
j3 = =TEXT(J2,"dd mm")

When j2 changes the tab name doesn't change until I click on J3 and press
enter.

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

Regards
--
Big Rick

--

Dave Peterson

Thanks Dave,
This does work better but I would like to take it one stage further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change automatically.
By the way, I really am clueless when it come to macros. Remember my post on
date formatting last week which started a great debate !) So please can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick


"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code checks to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you wanted.

Hi Rick

In case you are wanting to get on with this this morning, and Dave won't
(probably) be on line yet, you need to make changes
to
Const WS_RANGE As String = "j2" change to "j1"
as J1 is now the cell where you are making the change to the date.
and
Me.Name = format(Target.Value, "dd mm") to
Me.Name = format(Target.Value + 7, "dd mm")


--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks Dave,
This does work better but I would like to take it one stage further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with
Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change
automatically.
By the way, I really am clueless when it come to macros. Remember my
post on
date formatting last week which started a great debate !) So please
can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick


"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code checks
to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just
start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you
wanted.

Thanks for replying Roger.
This is still not quite what I'm after. I will try and explain differently.
Say Info!A1 is "1/1/06" and on Sheet2, J2 is "=Info!A1+7" which should make
the tab name "08 01"
If I were to change Info!A1 to 2/2/06 then I require Sheet2 tab name to
change straightaway to "09 02".
What I am having to do now is to actually click in Sheet2!J2 cell and press
enter to make the tab name change. Dave suggested something about a Worksheet
Calculate Event but as you know, I wouldn't know where to start.
Hoping that this explains further.
--
Big Rick


"Roger Govier" wrote:

Hi Rick

In case you are wanting to get on with this this morning, and Dave won't
(probably) be on line yet, you need to make changes
to
Const WS_RANGE As String = "j2" change to "j1"
as J1 is now the cell where you are making the change to the date.
and
Me.Name = format(Target.Value, "dd mm") to
Me.Name = format(Target.Value + 7, "dd mm")


--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks Dave,
This does work better but I would like to take it one stage further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with
Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change
automatically.
By the way, I really am clueless when it come to macros. Remember my
post on
date formatting last week which started a great debate !) So please
can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick


"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code checks
to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just
start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you
wanted.

Put the worksheet change into the info worksheet. But you'll have to use the
codename of the worksheet:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the sheet that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.



Big Rick wrote:

Thanks Dave,
This does work better but I would like to take it one stage further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change automatically.
By the way, I really am clueless when it come to macros. Remember my post on
date formatting last week which started a great debate !) So please can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick

"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code checks to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you wanted.

--

Dave Peterson

Hi Rick

Sorry, I hadn't appreciated it was 2 separate sheets.
The following seems to work OK for me

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet2")

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With ws1
.Name = Format(Target.Value + 7, "dd mm")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks for replying Roger.
This is still not quite what I'm after. I will try and explain
differently.
Say Info!A1 is "1/1/06" and on Sheet2, J2 is "=Info!A1+7" which should
make
the tab name "08 01"
If I were to change Info!A1 to 2/2/06 then I require Sheet2 tab name
to
change straightaway to "09 02".
What I am having to do now is to actually click in Sheet2!J2 cell and
press
enter to make the tab name change. Dave suggested something about a
Worksheet
Calculate Event but as you know, I wouldn't know where to start.
Hoping that this explains further.
--
Big Rick


"Roger Govier" wrote:

Hi Rick

In case you are wanting to get on with this this morning, and Dave
won't
(probably) be on line yet, you need to make changes
to
Const WS_RANGE As String = "j2" change to "j1"
as J1 is now the cell where you are making the change to the date.
and
Me.Name = format(Target.Value, "dd mm") to
Me.Name = format(Target.Value + 7, "dd mm")


--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks Dave,
This does work better but I would like to take it one stage
further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with
Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change
automatically.
By the way, I really am clueless when it come to macros. Remember
my
post on
date formatting last week which started a great debate !) So please
can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick


"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code
checks
to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just
start looking
at J2:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you
wanted.

Thank you, thank you, thank you, so much.
Absolutely wonderful. Works like a dream. Please can I ask just one more
question though.
If I wanted this on 52 worksheets in one workbook +7, +14, +21 etc
would I require 52 lines of
sheet1.Name = format(target.Value+7,"dd mm")
sheet2.Name = format(target.Value+14,"dd mm") etc
or is there an easier way.
Thank you again for all your time and effort. I really do appreciate it.
--
Big Rick


"Dave Peterson" wrote:

Put the worksheet change into the info worksheet. But you'll have to use the
codename of the worksheet:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the sheet that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.

You could do it that way. But another way is to go through the codenames and
set them that way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "a1"

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 mm")
Exit For
End If
Next iCtr
Next wks
End If

ws_exit:
Application.EnableEvents = True
End Sub

==============
But this doesn't sound like something that you'd do very often--once a year when
you're setting things up.

I think I'd remove it from the worksheet_change event and just make a macro that
runs on demand. It would stop user errors (overwriting A1 on Info could cause
trouble).

If you think that's a good idea and you have trouble converting it, just post
back. I'm sure you'll get help.




Big Rick wrote:

Thank you, thank you, thank you, so much.
Absolutely wonderful. Works like a dream. Please can I ask just one more
question though.
If I wanted this on 52 worksheets in one workbook +7, +14, +21 etc
would I require 52 lines of
sheet1.Name = format(target.Value+7,"dd mm")
sheet2.Name = format(target.Value+14,"dd mm") etc
or is there an easier way.
Thank you again for all your time and effort. I really do appreciate it.
--
Big Rick

"Dave Peterson" wrote:

Put the worksheet change into the info worksheet. But you'll have to use the
codename of the worksheet:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the sheet that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.


--

Dave Peterson

Thank you very much again.
I suppose you really are quite clever when I think about it.
--
Big Rick


"Dave Peterson" wrote:

You could do it that way. But another way is to go through the codenames and
set them that way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "a1"

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 mm")
Exit For
End If
Next iCtr
Next wks
End If

ws_exit:
Application.EnableEvents = True
End Sub

==============
But this doesn't sound like something that you'd do very often--once a year when
you're setting things up.

I think I'd remove it from the worksheet_change event and just make a macro that
runs on demand. It would stop user errors (overwriting A1 on Info could cause
trouble).

If you think that's a good idea and you have trouble converting it, just post
back. I'm sure you'll get help.




Big Rick wrote:

Thank you, thank you, thank you, so much.
Absolutely wonderful. Works like a dream. Please can I ask just one more
question though.
If I wanted this on 52 worksheets in one workbook +7, +14, +21 etc
would I require 52 lines of
sheet1.Name = format(target.Value+7,"dd mm")
sheet2.Name = format(target.Value+14,"dd mm") etc
or is there an easier way.
Thank you again for all your time and effort. I really do appreciate it.
--
Big Rick

"Dave Peterson" wrote:

Put the worksheet change into the info worksheet. But you'll have to use the
codename of the worksheet:

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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the sheet that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.


--

Dave Peterson

Thanks Roger.
Once again, I have not been let down. I thought this might proved a little
tricky, but you simply took it all in your stride.
I off work after today, so I am sure I will be in touch after a week or three.

Thanks again.
--
Big Rick


"Roger Govier" wrote:

Hi Rick

Sorry, I hadn't appreciated it was 2 separate sheets.
The following seems to work OK for me

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet2")

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With ws1
.Name = Format(Target.Value + 7, "dd mm")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hi Rick

Thanks for the response and kind words.
As is often the case the hard work was done by others, Dave P in this
case, and all I did was tinker around the edges.
Have a good break from work - but whose going to keep that 300,000
gallons of water clean now?<bg

--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks Roger.
Once again, I have not been let down. I thought this might proved a
little
tricky, but you simply took it all in your stride.
I off work after today, so I am sure I will be in touch after a week
or three.

Thanks again.
--
Big Rick


"Roger Govier" wrote:

Hi Rick

Sorry, I hadn't appreciated it was 2 separate sheets.
The following seems to work OK for me

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet2")

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With ws1
.Name = Format(Target.Value + 7, "dd mm")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub