How to name each sheet in the box
 

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

How to name each sheet in the box
 

I do not completey understand the situation, but it may be something like this:

In the upper left box in the Formula Bar the active Cell address is
displayed or the name of the Cell if it has been given a name.
If you select the Down-arrow on the right-side a drop-down list of all named
ranges is shown. If you select one, your pointer will go to this specific
range.
So if you give lets say all a1 cells in all your worksheets a specific name,
you may select the worksheet by selecting this name in the drop-down list.

regards,

Jan BArt

"Grd" wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

How to name each sheet in the box
 

I don't think it was the namebox. But they could have been using an addin that
gives them similar capabilities.

If you want to try one, start a new workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste this code
there.


Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
ctrl.AddItem wks.Name
Next wks
End Sub

Now back to excel and
file|saveas
choose save as type: Microsoft Office Excel Add-in (*.xla)
at the bottom of that dropdown.

Now close excel and reopen it.
Turn on the addin.
tools|addins
look for that workbookname you just created and put a check mark there.

You should see a toolbar that you can position where you want.

If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Grd wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

--

Dave Peterson

How to name each sheet in the box
 

ps. You can also right click on the VCR like controls above the status bar and
navigate to other worksheets that way.

Grd wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

--

Dave Peterson

How to name each sheet in the box
 

Grd

You can right-click on one of the sheet navigation arrows at lower left of
sheet tabs and get a pop up of sheets to select from.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 06:59:06 -0800, Grd wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

How to name each sheet in the box
 

Thanks that works

grd

"Excelerate-nl" wrote:

I do not completey understand the situation, but it may be something like this:

In the upper left box in the Formula Bar the active Cell address is
displayed or the name of the Cell if it has been given a name.
If you select the Down-arrow on the right-side a drop-down list of all named
ranges is shown. If you select one, your pointer will go to this specific
range.
So if you give lets say all a1 cells in all your worksheets a specific name,
you may select the worksheet by selecting this name in the drop-down list.

regards,

Jan BArt

"Grd" wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

How to name each sheet in the box
 

Great Tip.

Grd

"Gord Dibben" wrote:

Grd

You can right-click on one of the sheet navigation arrows at lower left of
sheet tabs and get a pop up of sheets to select from.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 06:59:06 -0800, Grd wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

How to name each sheet in the box
 

Neat Macro. Works nicely

Grd

"Dave Peterson" wrote:

I don't think it was the namebox. But they could have been using an addin that
gives them similar capabilities.

If you want to try one, start a new workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste this code
there.


Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
ctrl.AddItem wks.Name
Next wks
End Sub

Now back to excel and
file|saveas
choose save as type: Microsoft Office Excel Add-in (*.xla)
at the bottom of that dropdown.

Now close excel and reopen it.
Turn on the addin.
tools|addins
look for that workbookname you just created and put a check mark there.

You should see a toolbar that you can position where you want.

If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Grd wrote:

Hi there,

I used someones spreadsheet and they had the names of the sheets listed in
the box below the font at the top of the screen. I think its called the name
box.
I'd like to do that with my spreadsheet so I can switch sheets by clicking
the dropdown.

Anyone know how to do this?

Thanks in advance

Grd

--

Dave Peterson