donbowyer,
I think this will work, but please back up your workbook before doing any of
this.
The following relies on your worksheets' code names being set to be the same
as their tab names. The tab name is the name that shows in the worksheet
tab. Code names are separate names that can only be accessed through code.
The code name is shown first in the Project Explorer, e.g., for
Sheet1(Sheet1), the name outside of parentheses is the code name, the one in
the parentheses is the tab name. Code names cannot have spaces, so your
code names would have to be the same as the tab names, except that spaces in
the tab name are replaced by underscores in the code name. (This means that
you can't have underscores in your sheets' tab names or this code won't
work.) You can change all the code names by editing in the "Name" box in
the sheets' Properties windows.
Or here's a routine to do it. Put it in a standard module. Before you run
it you need to choose "Microsoft Basic for Applications Extensibility 5.3"
in the VBE Tools>References menu. If you're running XP or later you also
need to choose Tools>Macros>Security>Trusted Publishers>"Trust Access to
Visual Basic Project" in Excel, not in the VBE :
Sub set_codenames()
Dim vb_component As VBComponent
For Each vb_component In Application.VBE.ActiveVBProject.VBComponents
If vb_component.Type = vbext_ct_Document And vb_component.Name <>
"ThisWorkbook" Then
vb_component.Name =
WorksheetFunction.Substitute(vb_component.Properties("name"), " ", "_")
End If
Next vb_component
End Sub
Once you've changed all the code names, you should be able to get the
behavior you want by putting the following code in the workbook's
ThisWorkbook module. Note that your worksheet tab names should not have
underscores in them because this code will also replace them with blanks:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws Is ActiveSheet Then
ws.Name = WorksheetFunction.Substitute(ws.CodeName, "_", " ")
Else
ws.Name = ws.Index
End If
Next ws
End Sub
Whew, that's complicated, and may not work for reasons I haven't even
thought of. Hopefully, one of the experts will come along with a two-line
answer while I'm typing this.
hth,
Doug