ExcelBanter - Help with dropdown menus

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help with dropdown menus (https://www.excelbanter.com/excel-programming/338221-help-dropdown-menus.html)

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?

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?

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?

Help with dropdown menus