Navigation Command Bar
 

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

--
huntermc
-----------------------------------------------------------------------
huntermcg's Profile: http://www.excelforum.com/member.php...fo&userid=1939
View this thread: http://www.excelforum.com/showthread.php?threadid=47458

Navigation Command Bar
 

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.

--
HTH

Bob Phillips

"huntermcg" wrote
in message ...

This code puts a navigation bar in your workbook to easily navigate
between sheets. I found it at a site. The command bar opens when I put
the code under workbook in VBA. Only I am not an expert and do not
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 Sub


--
huntermcg
------------------------------------------------------------------------
huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
View this thread: http://www.excelforum.com/showthread...hreadid=474588

Navigation Command Bar
 

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

--
huntermc
-----------------------------------------------------------------------
huntermcg's Profile: http://www.excelforum.com/member.php...fo&userid=1939
View this thread: http://www.excelforum.com/showthread.php?threadid=47458

Navigation Command Bar
 

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



--
HTH

Bob Phillips

"huntermcg" wrote
in message ...

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

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

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


--
huntermcg
------------------------------------------------------------------------
huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
View this thread: http://www.excelforum.com/showthread...hreadid=474588

Navigation Command Bar
 

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 ?


--
huntermcg
------------------------------------------------------------------------
huntermcg's Profile: http://www.excelforum.com/member.php...o&userid=19391
View this thread: http://www.excelforum.com/showthread...hreadid=474588

Navigation Command Bar
 

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

--
HTH

Bob Phillips

"huntermcg" wrote
in message ...

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 ?


--
huntermcg
------------------------------------------------------------------------
huntermcg's Profile:
http://www.excelforum.com/member.php...o&userid=19391
View this thread: http://www.excelforum.com/showthread...hreadid=474588

Navigation Command Bar
 

Great! Thanks for your help Bob.

If you have time, can you look into this post of mine:
http://www.excelforum.com//showthrea...83&goto=newpos

--
huntermc
-----------------------------------------------------------------------
huntermcg's Profile: http://www.excelforum.com/member.php...fo&userid=1939
View this thread: http://www.excelforum.com/showthread.php?threadid=47458