Navigation Command Bar

[huntermcg]

This code puts a navigation bar in your workbook to easily navigat
between sheets. I found it at a site. The command bar opens when I pu
the code under workbook in VBA. Only I am not an expert and do no
really understand what to edit before I get it to work in my workbook
Can someone help me with this ??


Navigation Command Bar:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Navigate").Delete
On Error GoTo 0

With Application.CommandBars.Add("Navigate XL-Dennis", , False, True)

With .Controls.Add(msoControlButton)
.TooltipText = "Move Back"
.FaceId = 1017
.OnAction = "Move_Back"
.BeginGroup = True
End With

With .Controls.Add(msoControlDropdown)
.AddItem "Sheet1"
.AddItem "Sheet2"
.AddItem "Sheet3"
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With

With .Controls.Add(msoControlButton)
.TooltipText = "Move next"
.FaceId = 1018
.OnAction = "Move_Next"
End With

.Protection = msoBarNoCustomize
.Position = msoBarFloating
.Visible = True
End With
End Sub



Private Sub Sheet_Navigate()
Dim stActiveSheet As String

With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
End With

Select Case stActiveSheet
Case "Sheet1"
Worksheets("Shee1").Activate
Case "Sheet2"
Worksheets("Sheet2").Activate
Case "Sheet3"
Worksheets("Sheet3").Activate
End Select
End Sub

Private Sub Move_Back()
On Error Resume Next
ActiveSheet.Previous.Select
End Sub

Private Sub Move_Next()
On Error Resume Next
ActiveSheet.Next.Select
End Su

 

[Bob Phillips]

Have you added the code to the ThisWorkbook code module in your workbook?

If you add it to Personal.xls, it should be always available.

 

[huntermcg]

Yes. I added it in the ThisWorkbook Module and it is available. I se
the new toolbar. It only does not work.

I guess I need to alter the code a bit, because my sheets hav
different names then sheet1, sheet2 and sheet3. Also the back an
forward button give an error. Please tell what I need to change in th
code to make it work for my sheet ?

Do I also put the whole code in the thisworkbook module. Or do I nee
to split it up over different modules

 

[Bob Phillips]

Okay, but I would amend it to make it generic, and so that you don't need to
know the name of the sheets.

Put this code in the ThisWorkbook code module.

Private Sub Workbook_Open()
Dim sh As Object

On Error Resume Next
Application.CommandBars("Navigate XL-Dennis").Delete
Application.CommandBars("Navigate Sheets").Delete
On Error GoTo 0

With Application.CommandBars.Add("Navigate Sheets", , False, True)

With .Controls.Add(msoControlButton)
.TooltipText = "Move Back"
.FaceId = 1017
.OnAction = "Move_Back"
.BeginGroup = True
End With

With .Controls.Add(msoControlDropdown)
For Each sh In ThisWorkbook.Sheets
.AddItem sh.Name
Next sh
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With

With .Controls.Add(msoControlButton)
.TooltipText = "Move next"
.FaceId = 1018
.OnAction = "Move_Next"
End With

.Protection = msoBarNoCustomize
.Position = msoBarFloating
.Visible = True
End With
End Sub


and then put the rest of the code in a standard code module

Private Sub Sheet_Navigate()
Dim stActiveSheet As String

With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
ThisWorkbook.Sheets(stActiveSheet).Activate
End With
End Sub

Private Sub Move_Back()
On Error Resume Next
ActiveSheet.Previous.Select
End Sub

Private Sub Move_Next()
On Error Resume Next
ActiveSheet.Next.Select
End Sub

 

[huntermcg]

Great it works! Thanks for your help.

I have one last question about this toolbar. I want the size of the
dropdownbox in the toolbar with the sheet items larger, because it is
too small. Can we adjust the code for this ?

 

[Bob Phillips]

Change this bit of code

With .Controls.Add(msoControlDropdown)
For Each sh In ThisWorkbook.Sheets
.AddItem sh.Name
Next sh
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With

to this

With .Controls.Add(msoControlDropdown)
For Each sh In ThisWorkbook.Sheets
.AddItem sh.Name
Next sh
.Width = 400
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With

with a suitable value for Width

 

[huntermcg]

Great! Thanks for your help Bob.

If you have time, can you look into this post of mine:
http://www.excelforum.com//showthread.php?t=469683&goto=newpos