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.
|