Drop Down Menu Options? Need a PRO

[ibo4lyf]

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.php?do=newreply&noquote=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.

 

[Robert Morley]

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]

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!

 

[Robert Morley]

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