Navigation Command Bar

[huntermcg]

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[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.

--
HTH

Bob Phillips

"huntermcg" <huntermcg.1wn1ib_1128866704.7913@excelforum-nospam.com> wrote
in message news:huntermcg.1wn1ib_1128866704.7913@excelforum-nospam.com...
>
> 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
>

[huntermcg]

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 ?

[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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

--
HTH

Bob Phillips

"huntermcg" <huntermcg.1wnfea_1128884716.7658@excelforum-nospam.com> wrote
in message news:huntermcg.1wnfea_1128884716.7658@excelforum-nospam.com...
>
> 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
>

[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

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

with a suitable value for Width

--
HTH

Bob Phillips

"huntermcg" <huntermcg.1woiab_1128935118.4461@excelforum-nospam.com> wrote
in message news:huntermcg.1woiab_1128935118.4461@excelforum-nospam.com...
>
> 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
>

[huntermcg]

Great! Thanks for your help Bob.

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