[dba-Tech] Excel sheet tabs & reference to it
Paul Hartland
paul.hartland at googlemail.com
Thu May 10 16:38:14 CDT 2018
John,
I think that I may be thinking of this too simplistically.......I have just
had a quick look at your Excel sheet.....
If for example I changed the name on the roster tab from Andon to
John....you would want all the unprotected cells in the Andon sheet to be
deleted and the sheet 'Andon' to change to 'John' and all the references in
the totals sheet for 'Andon' to now reference the newly renamed 'John'
sheet ?
Is that about it ?
Paul
On 10 May 2018 at 21:58, John Bartow <jbartow at winhaven.net> wrote:
> 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
>
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>
--
Paul Hartland
paul.hartland at googlemail.com
More information about the dba-Tech
mailing list