Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.modulesdaovba
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit Drop Down Menu Options Among Cells | Excel Discussion (Misc queries) | |||
Displaying Drop-Down Menu Options | Excel Discussion (Misc queries) | |||
how do i choose multiple options in a drop down menu in excel? | Excel Discussion (Misc queries) | |||
Drop-down menu options | Excel Discussion (Misc queries) | |||
How do I increase the font of my drop-down menu options? | Excel Worksheet Functions |