Tab Names-Private Sub Worksheet_Change

I've taken a suggested VBA code from the discussion groups;

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$5" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$5" Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub


This as you can see changes the tab name into cell B5. My problem now is B5
on each tab used it linked into a lookup forumla which looks onto an options
tab, which defines where to look for each code. These formulas work fine and
change through to all the right numbers on each B5 cell. However the Tab
name doesnt change until I actually go into that cell and link back into A2
which is the cells its linked to.

Hope that all makes sense. Any suggestions or if you need more info let me
know

Little amendment, I didnt mean to post the VBA code twice, its just in there
the once

Cheers
Tom

"Tom Hewitt" wrote:

> I've taken a suggested VBA code from the discussion groups;
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = "$B$5" Then
> Me.Name = Target.Value
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = "$B$5" Then
> Me.Name = Target.Value
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> This as you can see changes the tab name into cell B5. My problem now is B5
> on each tab used it linked into a lookup forumla which looks onto an options
> tab, which defines where to look for each code. These formulas work fine and
> change through to all the right numbers on each B5 cell. However the Tab
> name doesnt change until I actually go into that cell and link back into A2
> which is the cells its linked to.
>
> Hope that all makes sense. Any suggestions or if you need more info let me
> know

Worksheet_change reacts nicely to typing changes--but not to changes because of
recalculation.

Option Explicit
Private Sub Worksheet_Calculate()

On Error GoTo ws_exit:
Application.EnableEvents = False
If LCase(Me.Name) <> LCase(Me.Range("B5").Value) Then
Me.Name = Me.Range("b5").Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

But if you're duplicating this code in each worksheet module, maybe just having
it once behind the ThisWorkbook would be better.

Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
On Error GoTo ws_exit:
Application.EnableEvents = False
If LCase(Sh.Name) <> LCase(Sh.Range("B5").Value) Then
Sh.Name = Sh.Range("b5").Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Tom Hewitt wrote:
>
> I've taken a suggested VBA code from the discussion groups;
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = "$B$5" Then
> Me.Name = Target.Value
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = "$B$5" Then
> Me.Name = Target.Value
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> This as you can see changes the tab name into cell B5. My problem now is B5
> on each tab used it linked into a lookup forumla which looks onto an options
> tab, which defines where to look for each code. These formulas work fine and
> change through to all the right numbers on each B5 cell. However the Tab
> name doesnt change until I actually go into that cell and link back into A2
> which is the cells its linked to.
>
> Hope that all makes sense. Any suggestions or if you need more info let me
> know

--

Dave Peterson