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.