Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic menus
Im working on a excel project where i need some kind of menu to navigate
through weeks and days. There is one excel page for each day of the week and all 52 weeks included in one excel document, there is also a sumary. I need a way of doing a menu so when you click on for example week 6 Monday-Sunday for that week comes up, perferably as a "sub-menu". Is there any way of either making a menu with VBA or macros to have a meni come up/down for a specific week so if you have tabs or buttons for week 1-52 and you click on one of them a new menu comes forth. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic menus
Is this what you mean?
Private Sub AddMenu() With Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, temporary:=True) .Caption = "Weeks" With .Controls.Add(Type:=msoControlPopup) .Caption = "Week1" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With With .Controls.Add(Type:=msoControlPopup) .Caption = "Week2" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With 'etc. End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tobie" wrote in message ... Im working on a excel project where i need some kind of menu to navigate through weeks and days. There is one excel page for each day of the week and all 52 weeks included in one excel document, there is also a sumary. I need a way of doing a menu so when you click on for example week 6 Monday-Sunday for that week comes up, perferably as a "sub-menu". Is there any way of either making a menu with VBA or macros to have a meni come up/down for a specific week so if you have tabs or buttons for week 1-52 and you click on one of them a new menu comes forth. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic menus
That was accually a better way of doing it then the way i tought of, but is
there a way to automaticly load the menu when you open the excel document? "Bob Phillips" wrote: Is this what you mean? Private Sub AddMenu() With Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, temporary:=True) .Caption = "Weeks" With .Controls.Add(Type:=msoControlPopup) .Caption = "Week1" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With With .Controls.Add(Type:=msoControlPopup) .Caption = "Week2" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With 'etc. End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tobie" wrote in message ... Im working on a excel project where i need some kind of menu to navigate through weeks and days. There is one excel page for each day of the week and all 52 weeks included in one excel document, there is also a sumary. I need a way of doing a menu so when you click on for example week 6 Monday-Sunday for that week comes up, perferably as a "sub-menu". Is there any way of either making a menu with VBA or macros to have a meni come up/down for a specific week so if you have tabs or buttons for week 1-52 and you click on one of them a new menu comes forth. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic menus
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars(1).Controls("Weeks").Delet e On Error GoTo 0 End Sub Private Sub Workbook_Open() On Error Resume Next Application.CommandBars(1).Controls("Weeks").Delet e On Error GoTo 0 Call AddMenu End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tobie" wrote in message ... That was accually a better way of doing it then the way i tought of, but is there a way to automaticly load the menu when you open the excel document? "Bob Phillips" wrote: Is this what you mean? Private Sub AddMenu() With Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, temporary:=True) .Caption = "Weeks" With .Controls.Add(Type:=msoControlPopup) .Caption = "Week1" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With With .Controls.Add(Type:=msoControlPopup) .Caption = "Week2" With .Controls.Add(Type:=msoControlButton) .Caption = "Monday" .OnAction = "Macro1" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Tuesday" .OnAction = "Macro2" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Wednesday" .OnAction = "Macro3" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Thursday" .OnAction = "Macro4" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Friday" .OnAction = "Macro5" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Saturday" .OnAction = "Macro6" End With With .Controls.Add(Type:=msoControlButton) .Caption = "Sunday" .OnAction = "Macro7" End With End With 'etc. End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tobie" wrote in message ... Im working on a excel project where i need some kind of menu to navigate through weeks and days. There is one excel page for each day of the week and all 52 weeks included in one excel document, there is also a sumary. I need a way of doing a menu so when you click on for example week 6 Monday-Sunday for that week comes up, perferably as a "sub-menu". Is there any way of either making a menu with VBA or macros to have a meni come up/down for a specific week so if you have tabs or buttons for week 1-52 and you click on one of them a new menu comes forth. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub-menus of main menus for Excel 2007 | New Users to Excel | |||
Dynamic drop-down menus with PivotCharts | Charts and Charting in Excel | |||
difference between word 2003 menus and word 2007 menus-Explain pl | Excel Discussion (Misc queries) | |||
New Menus - attaching but menus are reset | Excel Worksheet Functions | |||
Overriding Actions of Excel DropDown Menus and Poup Menus | Excel Programming |