How to name each sheet in the box

G

Grd

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
 
E

Excelerate-nl

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
 
D

Dave Peterson

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
 
D

Dave Peterson

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

Gord Dibben

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
 
G

Grd

Thanks that works

grd

Excelerate-nl said:
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
Click to expand...
 
G

Grd

Great Tip.

Grd

Gord Dibben said:
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
Click to expand...
 
G

Grd

Neat Macro. Works nicely

Grd

Dave Peterson said:
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
Click to expand...
 
Top