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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.modulesdaovba
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.modulesdaovba
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access.modulesdaovba
|
|||
|
|||
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! |
Reply |
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 |