Worksheet Tabs - Names & Numbers
 

I have a workbook with many named worksheets.
The sheets are selected from a ListBox which shows their names.
Is it possible, programmatically, to cause the newly selected sheet's tab to
show the sheet name and the previously selected sheet's tab to revert to the
sheet's index number?
Thus at any given time, the view will be all tabs showing a number and just
one with a name.
--
donwb

--
donwb

Worksheet Tabs - Names & Numbers
 

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 ToolsReferences menu. If you're running XP or later you also
need to choose ToolsMacrosSecurityTrusted 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.Properti es("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


"donbowyer" wrote in message
...
I have a workbook with many named worksheets.
The sheets are selected from a ListBox which shows their names.
Is it possible, programmatically, to cause the newly selected sheet's tab
to
show the sheet name and the previously selected sheet's tab to revert to
the
sheet's index number?
Thus at any given time, the view will be all tabs showing a number and
just
one with a name.
--
donwb

--
donwb

Worksheet Tabs - Names & Numbers
 

Thanks very much for that Doug.
I've read it. Now I will digest it, then try it.
I see where your going.
I'll let you know.
--
donwb


"Doug Glancy" wrote:

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 ToolsReferences menu. If you're running XP or later you also
need to choose ToolsMacrosSecurityTrusted 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.Properti es("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


"donbowyer" wrote in message
...
I have a workbook with many named worksheets.
The sheets are selected from a ListBox which shows their names.
Is it possible, programmatically, to cause the newly selected sheet's tab
to
show the sheet name and the previously selected sheet's tab to revert to
the
sheet's index number?
Thus at any given time, the view will be all tabs showing a number and
just
one with a name.
--
donwb

--
donwb