Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Tab Names
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
Tab Names II | Excel Discussion (Misc queries) | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
How can I find the common names in two columns of names? | Excel Discussion (Misc queries) |