Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |