Drop Down Menu Options? Need a PRO
 

Big thanks to groups user huntermcg for this find.

I have entered this into my "ThisWorkbook" -

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 I entered this into a 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


This created a neat little drop down menu for sheet navigation.
(thanks to Bob Phillips for the code at
http://www.excelforum.com/newreply.p...te=1&p=1358771)


QUESTION -

I've applied this to a project of mine and it works great.

Although with my project I have a workbook with over 250 sheets :-)

Is their a way to limit the amount of sheets displayed in the drop
down box?

also if the above is possible...

Is their a way to have different set of sheets displayed for each
worksheet?

For example, on "sheet 1", the drop down only displays sheets 2-5.
Upon the users arrival to sheet 4, the drop down menu displays sheets
30-50.

Thanks for the help in advance.

Drop Down Menu Options? Need a PRO
 

That sounds to me like you would want two drop-downs. One for the initial
sheet selected and one to display the extra sheets once the first sheet is
chosen.

Alternatively, you could control the drop-down entirely programatically and
only add those items that you want to display at any given moment. (In
other words, replace the For Each sh loop with something a little more
"choosy".)


Rob

ibo4lyf wrote:
Big thanks to groups user huntermcg for this find.

I have entered this into my "ThisWorkbook" -

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 I entered this into a 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


This created a neat little drop down menu for sheet navigation.
(thanks to Bob Phillips for the code at
http://www.excelforum.com/newreply.p...te=1&p=1358771)


QUESTION -

I've applied this to a project of mine and it works great.

Although with my project I have a workbook with over 250 sheets :-)

Is their a way to limit the amount of sheets displayed in the drop
down box?

also if the above is possible...

Is their a way to have different set of sheets displayed for each
worksheet?

For example, on "sheet 1", the drop down only displays sheets 2-5.
Upon the users arrival to sheet 4, the drop down menu displays sheets
30-50.

Thanks for the help in advance.

Drop Down Menu Options? Need a PRO
 

I hope it's not too much to ask for an example.

I am looking for limited options in the menu.

When the user is on the home page, I would like the drop down to give
only five options (worksheets). When the user chooses one and arrives
at that worksheet, the drop down will give another set of worksheets
to choose from.

Thanks again! Google groups is the best!

Drop Down Menu Options? Need a PRO
 

I'm honestly not sure how you'd do that when changing sheets in Excel. I
haven't done much work with command bars, and only in Access so far.


Rob

ibo4lyf wrote:
I hope it's not too much to ask for an example.

I am looking for limited options in the menu.

When the user is on the home page, I would like the drop down to give
only five options (worksheets). When the user chooses one and arrives
at that worksheet, the drop down will give another set of worksheets
to choose from.

Thanks again! Google groups is the best!