Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
Hi Dave -
Found your NavToolbar add-in on Contextures.com -- a VERY useful tool, for sure. However, I noticed that any hidden worksheets are not listed in the drop-down. Is there a way to modify the code to allow them to be listed also, perhaps in italics (or some other identifying way)? thanks alot, ray |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
I don't think I'd do this--The reason I hide sheets is to keep the workbook less
cluttered. Stuff that I need, but don't need to see goes on those hidden sheets. But if you want, this worked for me. But don't use a worksheet name like: sheet1--hidden I use that to indicate the hidden-ness of the sheet. Option Explicit Sub Auto_Close() On Error Resume Next Application.CommandBars("MyNavigator").Delete On Error GoTo 0 End Sub Sub Auto_Open() 'code written by Dave Peterson 2005-12-21 'creates a toolbar with list of sheets in active workbook Dim cb As CommandBar Dim ctrl As CommandBarControl 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 Object Dim IsHidden As Boolean With Application.CommandBars.ActionControl If .ListIndex = 0 Then MsgBox "Please select an existing sheet" Exit Sub Else myWksName = .List(.ListIndex) End If End With If LCase(myWksName) Like LCase("*--hidden") Then myWksName = Left(myWksName, Len(myWksName) - Len("--hidden")) End If Set wks = Nothing On Error Resume Next Set wks = Sheets(myWksName) On Error GoTo 0 If wks Is Nothing Then Call RefreshTheSheets MsgBox "Please try again" Else wks.Visible = xlSheetVisible wks.Select End If End Sub Sub RefreshTheSheets() Dim ctrl As CommandBarControl Dim wks As Object Dim myMsg As String Set ctrl = Application.CommandBars("myNavigator") _ .FindControl(Tag:="__wksnames__") ctrl.Clear For Each wks In ActiveWorkbook.Sheets If wks.Visible = xlSheetVisible Then myMsg = "" Else myMsg = "--Hidden" End If ctrl.AddItem wks.Name & myMsg Next wks End Sub Ray wrote: Hi Dave - Found your NavToolbar add-in on Contextures.com -- a VERY useful tool, for sure. However, I noticed that any hidden worksheets are not listed in the drop-down. Is there a way to modify the code to allow them to be listed also, perhaps in italics (or some other identifying way)? thanks alot, ray -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
Thanks Dave, I appreciate the code ...
I would normally agree -- it's hidden for a reason. The reason that I asked anyway was this: I have a rather extensive 'application' (it does alot!) that is distributed to a large number of people. There are approx 10-12 hidden sheets, each one either holding static data or performing intermediate calculations. If/when I need to update/modify/ add something, being able to access the hidden sheets via the NavToolbar will make doing so much easier... Plus, I'm not afraid to admit that *sometimes* I forget that there are hidden sheets.... :) thanks again, ray |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
I kind of figured that it was a development tool that you needed.
I wouldn't share it with users, though. The next thing you know, those sheets are unhidden, renamed, deleted, modified, in other words "fixed!". Ray wrote: Thanks Dave, I appreciate the code ... I would normally agree -- it's hidden for a reason. The reason that I asked anyway was this: I have a rather extensive 'application' (it does alot!) that is distributed to a large number of people. There are approx 10-12 hidden sheets, each one either holding static data or performing intermediate calculations. If/when I need to update/modify/ add something, being able to access the hidden sheets via the NavToolbar will make doing so much easier... Plus, I'm not afraid to admit that *sometimes* I forget that there are hidden sheets.... :) thanks again, ray -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
Hi Dave -
Is there an easy way to add this to a drop-down menu (like Tools)? I've run into a couple of times where I wanted to temporarily stop using it, but once it's closed, I can't re-open without closing Excel and then re-opening. I know I can move it 'out of the way', but this isn't always optimal ... Also, how do I 'hide/show quoted text'? I don't see an option for that anywhere... TIA, ray |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
If you only closed the toolbar, you can just reshow it.
View|toolbars and check myNavigator Ray wrote: Hi Dave - Is there an easy way to add this to a drop-down menu (like Tools)? I've run into a couple of times where I wanted to temporarily stop using it, but once it's closed, I can't re-open without closing Excel and then re-opening. I know I can move it 'out of the way', but this isn't always optimal ... Also, how do I 'hide/show quoted text'? I don't see an option for that anywhere... TIA, ray -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
Hi Dave -
Is there a way to tell the NavToolbar to always open docked at the bottom of the excel window? It's become habit for me to put it there each time I open Excel ... Thanks, ray |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
NavToolbar ? for Dave Peterson
Add one line:
With cb .Visible = True .Position = msoBarBottom '<-- Added Ray wrote: Hi Dave - Is there a way to tell the NavToolbar to always open docked at the bottom of the excel window? It's become habit for me to put it there each time I open Excel ... Thanks, ray -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dave Peterson | Excel Discussion (Misc queries) | |||
Dave Peterson | Excel Discussion (Misc queries) | |||
Mr Dave peterson, Please help | Excel Discussion (Misc queries) | |||
Dave Peterson | Excel Discussion (Misc queries) | |||
to Dave Peterson | Excel Discussion (Misc queries) |