Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with dropdown menus
Hi. I am trying to use the combo box feature to navigate between
sheets. In other words, I have a dropdown menu on the header of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. I have gotten this far: Sub DropDown() If Worksheets("MainMenu").DropDow*n.Value = "Main Menu" Then Worksheets("Main Menu").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Goals" Then Worksheets("Goals").Range("A1"*).Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Development Plan" Then Worksheets("Development Plan").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Mid-Year" Then Worksheets("Mid-Year").Range("*A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Self-Evaluation" Then Worksheets("Self-Eval").Range(*"A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Functional Manager" Then Worksheets("Functional Mgr").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Manager" Then Worksheets("Manager").Range("A*1").Select End If End Sub I keep getting a compile error. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with dropdown menus
If you right-click the navigation buttons to the left of the sheet tabs, you
will see a built-in menu of the sheets. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Hi. I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu on the header of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. I have gotten this far: Sub DropDown() If Worksheets("MainMenu").DropDow*n.Value = "Main Menu" Then Worksheets("Main Menu").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Goals" Then Worksheets("Goals").Range("A1"*).Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Development Plan" Then Worksheets("Development Plan").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Mid-Year" Then Worksheets("Mid-Year").Range("*A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Self-Evaluation" Then Worksheets("Self-Eval").Range(*"A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Functional Manager" Then Worksheets("Functional Mgr").Range("A1").Select ElseIf Worksheets("MainMenu").DropDow*n.Value = "Manager" Then Worksheets("Manager").Range("A*1").Select End If End Sub I keep getting a compile error. What am I doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with dropdown menus
Hi Mia,
You can use the below code as a starting point. Customize it to your needs. Create a new module and paste the below code in it. You may call the createMenu routine on the workbook_open event to add it when opening the file. Call the deleteMenu on the workbook_beforeclose event to remove it. Customize the MenuActionx routines to your needs. You can add other actions if you want too. Good luck. Carlos Lozano www.caxonline.net '--------- Code ------------------- Public Sub CreateMenu() Dim oToolBar As CommandBar Dim oCommandBars As CommandBars Dim oButton As CommandBarButton Dim oPopUp As CommandBarPopup On Error Resume Next Set oCommandBars = Application.CommandBars oCommandBars.DisplayTooltips = True On Error GoTo 0 Set oToolBar = oCommandBars.Add("NewToolBar", msoBarTypeMenuBar) ' Create Popup and buttons for dialogs Set oPopUp = oToolBar.Controls.Add(Office.msoControlPopup) oPopUp.BeginGroup = True oPopUp.Caption = "New Menu" ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .BeginGroup = True .Style = msoButtonCaption .Caption = "Menu Option 1" .OnAction = "MenuAction1" End With ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .Style = msoButtonCaption .Caption = "Menu Option 2" .OnAction = "MenuAction2" End With oToolBar.Visible = True End Sub Public Sub DeleteMenu() Dim oCommandBars As CommandBars Set oCommandBars = Application.CommandBars oCommandBars("NewToolBar").Delete End Sub Public Sub MenuAction1() MsgBox "Action 1" End Sub Public Sub MenuAction2() MsgBox "Action 2" End Sub ' ---------- End of Code ---------- " wrote: Hi. I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu on the header of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. I have gotten this far: Sub DropDown() If Worksheets("MainMenu").DropDowÂ*n.Value = "Main Menu" Then Worksheets("Main Menu").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Goals" Then Worksheets("Goals").Range("A1"Â*).Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Development Plan" Then Worksheets("Development Plan").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Mid-Year" Then Worksheets("Mid-Year").Range("Â*A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Self-Evaluation" Then Worksheets("Self-Eval").Range(Â*"A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Functional Manager" Then Worksheets("Functional Mgr").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Manager" Then Worksheets("Manager").Range("AÂ*1").Select End If End Sub I keep getting a compile error. What am I doing wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with dropdown menus
Hi Mia,
You can use the below code as a starting point. Customize it to your needs. Create a new module and paste the below code in it. You may call the createMenu routine on the workbook_open event to add it when opening the file. Call the deleteMenu on the workbook_beforeclose event to remove it. Customize the MenuActionx routines to your needs. You can add other actions if you want too. Good luck. Carlos Lozano www.caxonline.net '--------- Code ------------------- Public Sub CreateMenu() Dim oToolBar As CommandBar Dim oCommandBars As CommandBars Dim oButton As CommandBarButton Dim oPopUp As CommandBarPopup On Error Resume Next Set oCommandBars = Application.CommandBars oCommandBars.DisplayTooltips = True On Error GoTo 0 Set oToolBar = oCommandBars.Add("NewToolBar", msoBarTypeMenuBar) ' Create Popup and buttons for dialogs Set oPopUp = oToolBar.Controls.Add(Office.msoControlPopup) oPopUp.BeginGroup = True oPopUp.Caption = "New Menu" ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .BeginGroup = True .Style = msoButtonCaption .Caption = "Menu Option 1" .OnAction = "MenuAction1" End With ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .Style = msoButtonCaption .Caption = "Menu Option 2" .OnAction = "MenuAction2" End With oToolBar.Visible = True End Sub Public Sub DeleteMenu() Dim oCommandBars As CommandBars Set oCommandBars = Application.CommandBars oCommandBars("NewToolBar").Delete End Sub Public Sub MenuAction1() MsgBox "Action 1" End Sub Public Sub MenuAction2() MsgBox "Action 2" End Sub ' ---------- End of Code ---------- " wrote: Hi. I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu on the header of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. I have gotten this far: Sub DropDown() If Worksheets("MainMenu").DropDowÂ*n.Value = "Main Menu" Then Worksheets("Main Menu").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Goals" Then Worksheets("Goals").Range("A1"Â*).Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Development Plan" Then Worksheets("Development Plan").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Mid-Year" Then Worksheets("Mid-Year").Range("Â*A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Self-Evaluation" Then Worksheets("Self-Eval").Range(Â*"A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Functional Manager" Then Worksheets("Functional Mgr").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Manager" Then Worksheets("Manager").Range("AÂ*1").Select End If End Sub I keep getting a compile error. What am I doing wrong? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with dropdown menus
Hi Mia,
You can use the below code as a starting point. Customize it to your needs. Create a new module and paste the below code in it. You may call the createMenu routine on the workbook_open event to add it when opening the file. Call the deleteMenu on the workbook_beforeclose event to remove it. Customize the MenuActionx routines to your needs. You can add other actions if you want too. Good luck. Carlos Lozano www.caxonline.net '--------- Code ------------------- Public Sub CreateMenu() Dim oToolBar As CommandBar Dim oCommandBars As CommandBars Dim oButton As CommandBarButton Dim oPopUp As CommandBarPopup On Error Resume Next Set oCommandBars = Application.CommandBars oCommandBars.DisplayTooltips = True On Error GoTo 0 Set oToolBar = oCommandBars.Add("NewToolBar", msoBarTypeMenuBar) ' Create Popup and buttons for dialogs Set oPopUp = oToolBar.Controls.Add(Office.msoControlPopup) oPopUp.BeginGroup = True oPopUp.Caption = "New Menu" ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .BeginGroup = True .Style = msoButtonCaption .Caption = "Menu Option 1" .OnAction = "MenuAction1" End With ' Add menu actions Set oButton = oPopUp.Controls.Add(Office.msoControlButton) With oButton .Style = msoButtonCaption .Caption = "Menu Option 2" .OnAction = "MenuAction2" End With oToolBar.Visible = True End Sub Public Sub DeleteMenu() Dim oCommandBars As CommandBars Set oCommandBars = Application.CommandBars oCommandBars("NewToolBar").Delete End Sub Public Sub MenuAction1() MsgBox "Action 1" End Sub Public Sub MenuAction2() MsgBox "Action 2" End Sub ' ---------- End of Code ---------- " wrote: Hi. I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu on the header of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. I have gotten this far: Sub DropDown() If Worksheets("MainMenu").DropDowÂ*n.Value = "Main Menu" Then Worksheets("Main Menu").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Goals" Then Worksheets("Goals").Range("A1"Â*).Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Development Plan" Then Worksheets("Development Plan").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Mid-Year" Then Worksheets("Mid-Year").Range("Â*A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Self-Evaluation" Then Worksheets("Self-Eval").Range(Â*"A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Functional Manager" Then Worksheets("Functional Mgr").Range("A1").Select ElseIf Worksheets("MainMenu").DropDowÂ*n.Value = "Manager" Then Worksheets("Manager").Range("AÂ*1").Select End If End Sub I keep getting a compile error. What am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in-cell dropdown menus | Excel Discussion (Misc queries) | |||
Excel dropdown menus | Setting up and Configuration of Excel | |||
Adding dropdown menus? | Excel Discussion (Misc queries) | |||
Checkboxes and DropDown menus | Excel Programming | |||
Overriding Actions of Excel DropDown Menus and Poup Menus | Excel Programming |