Dynamic menus

[Guest]

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.

 

[Bob Phillips]

Is this what you mean?

Private Sub AddMenu()
With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup,
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)

 

[Guest]

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]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(1).Controls("Weeks").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars(1).Controls("Weeks").Delete
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)