[dba-Tech] Excel sheet tabs & reference to it
John Bartow
jbartow at winhaven.net
Thu May 10 18:13:41 CDT 2018
Hi Paul,
Each coach has to do this only at the beginning of the season when the know their players names. There are 2 separate issues.
1. I thought if I put a button somewhere that deleted all the user data (non-locked cells) that it would be easier for them to make sure they cleaned the form at the beginning of the year. Most of them can do that by simply going to each sheet and highlighting and deleting but some of them have to ask a kid to do it. This has to be a purposeful click with a warning though not something as simple as you suggested. Great suggestion but if they spelled a kid's name wrong and finally noticed half way through the season and corrected it that would be bad ;-)
2. So What I'd like to happen when they put the names in the roster sheet is that it also populates all of the other sheets with their names. It does that now.
What I also want it to do is change the Tab name to that player's name. That it doesn't do now. And honestly, sheet tabs are about the weirdest thing to rename in the office suite.
By kicking off a procedure that does that when the name changes it would be painless and not dangerous :-)
-----Original Message-----
From: dba-Tech <dba-tech-bounces at databaseadvisors.com> On Behalf Of Paul Hartland via dba-Tech
Sent: Thursday, May 10, 2018 4:38 PM
To: Discussion of Hardware and Software issues <dba-tech at databaseadvisors.com>
Cc: Paul Hartland <paul.hartland at googlemail.com>
Subject: Re: [dba-Tech] Excel sheet tabs & reference to it
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
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com
More information about the dba-Tech
mailing list