[dba-Tech] Excel sheet tabs & reference to it

[John Bartow]

If Cell B2, on every individual players sheet on which I want the tab name to change, is updated by the formula in it that copies the name from the main roster sheet, could I have an event triggered by that cell change run your Sub routine "RenameWorksheet" using the same sheet's index and the text attribute of cell B2 as the input? 

By doing that the tab would automatically update without the user Doing anything other than typing in the new roster. Something like:
1. User types in new player name on Sheet(Roster)
2. Player's Individual sheet updates Cell B2 with player's name (due to cell formula =Roster!B3&" "&Roster!C3)
3. Cell change triggers Worksheet_Change - RenameWorksheet(ThisWorksheet(Index), Range("B2"))

-----Original Message-----
From: dba-Tech <dba-tech-bounces at databaseadvisors.com> On Behalf Of Gustav Brock
Sent: Thursday, May 10, 2018 2:13 AM
To: DBA-Tech (dba-tech at databaseadvisors.com) <dba-tech at databaseadvisors.com>; DBA Office (dba-office at databaseadvisors.com) <dba-office at databaseadvisors.com>
Subject: Re: [dba-Tech] Excel sheet tabs & reference to it

Hi John

It's not completely clear to me what you try to do, but I guess you can use these functions:

<code>
' Searches in the collection of worksheets of a workbook for ' a worksheet named Name or with a name starting with Name.
' Returns the index of the worksheet if found.
' Returns zero (0) if the worksheet name is empty or not found.
'
' To lookup the index of a worksheet with an exact name, use:
'
'   Index = ThisWorkbook.Worksheets("Exact Name").Index
'
' 2017-09-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function WorksheetIndex( _
    ByRef Workbook As Workbook, _
    ByVal Name As String) _
    As Integer

    Dim Worksheet   As Excel.Worksheet
    Dim Index       As Integer

    If Workbook Is Nothing Then
        ' Nothing to do.
        Exit Function
    ElseIf Name <> "" Then
        ' Loop worksheets.
        For Each Worksheet In Workbook.Worksheets
            If InStr(1, Worksheet.Name, Name, vbTextCompare) = 1 Then
                Index = Worksheet.Index
                Exit For
            End If
        Next
    End If

    Set Worksheet = Nothing

    WorksheetIndex = Index

End Function


' Renames a worksheet in this workbook.
'
' 2017-02-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RenameWorksheet( _
    ByVal Index As Variant, _
    ByVal Name As String)

    CleanWorksheetName Name
    ThisWorkbook.Worksheets(Index).Name = Name
    
End Sub


' Replaces characters in Name that are not allowed in a worksheet name.
' Truncates length of Name to MaxWorksheetNameLength.
' Returns the cleaned name by reference.
'
' 2017-02-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub CleanWorksheetName(ByRef Name As String)

    ' No special error handling.
    On Error Resume Next
    
    ' Maximum length of a worksheet name in Excel.
    Const MaxWorksheetNameLength    As Long = 31
    ' String containing all not allowed characters.
    Const InvalidCharacters         As String = "\/:*?<>[]"
    ' Character to replace not allowed characters.
    Const ReplaceCharacter          As String * 1 = "-"
    ' Character ellipsis: ..
    Const Ellipsis                  As String * 1 = "."
    
    Dim Length        As Integer
    Dim Position      As Integer
    Dim Character     As String
    Dim TrimmedName   As String
    
    ' Strip doubled spaces.
    While InStr(Name, Space(2)) > 0
        Name = Replace(Name, Space(2), Space(1))
    Wend
    ' Strip leading and trailing spaces.
    TrimmedName = Trim(Name)
    ' Limit length of name.
    If Len(TrimmedName) > MaxWorksheetNameLength Then
        TrimmedName = Left(TrimmedName, MaxWorksheetNameLength - 1) & Ellipsis
    End If
    Length = Len(TrimmedName)
    For Position = 1 To Length Step 1
        Character = Mid(TrimmedName, Position, 1)
        If InStr(InvalidCharacters, Character) > 0 Then
            Mid(TrimmedName, Position) = ReplaceCharacter
        End If
    Next
    
    ' Return cleaned name.
    Name = TrimmedName

End Sub
</code>

/gustav

________________________________________
Fra: dba-Tech <dba-tech-bounces at databaseadvisors.com> på vegne af John Bartow <jbartow at winhaven.net>
Sendt: 10. maj 2018 07:19:25
Til: DBA-Tech (dba-tech at databaseadvisors.com); DBA Office       (dba-office at databaseadvisors.com)
Emne: [dba-Tech] Excel sheet tabs & reference to it

I have a baseball roster, player stats and team stats. Each player gets a sheet with all of their stats per game. That sheet has the players name as the sheet tab name.

At the end of each season all I have to do is type in the new player roster and everything works. Except the Tab names and because the sheets are references for the team totals, the tab names are in the totals function's reference.

Is there some method of finding and replacing a word in the entire workbook - including the name of the sheet tabs?

Or is there a method to reference the tab without using the tab's name?

Thanks,
John B
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com