NavToolbar ? for Dave Peterson

[Ray]

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]

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

--

Dave Peterson

[Ray]

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]

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!".

--

Dave Peterson

[Ray]

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]

If you only closed the toolbar, you can just reshow it.
View|toolbars and check myNavigator

--

Dave Peterson

[Ray]

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]

Add one line:

With cb
.Visible = True

.Position = msoBarBottom '<-- Added

--

Dave Peterson