Help with dropdown menus

[email protected] · 08-24-2005, 04:05 PM

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=ADn.Value =3D "Main Menu" Then
Worksheets("Main Menu").Range("A1").Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Goals" Then
Worksheets("Goals").Range("A1"=AD).Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Development Plan"
Then
Worksheets("Development Plan").Range("A1").Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Mid-Year" Then
Worksheets("Mid-Year").Range("=ADA1").Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Self-Evaluation" Then

Worksheets("Self-Eval").Range(=AD"A1").Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Functional Manager"
Then Worksheets("Functional Mgr").Range("A1").Select
ElseIf Worksheets("MainMenu").DropDow=ADn.Value =3D "Manager" Then
Worksheets("Manager").Range("A=AD1").Select
End If

End Sub=20

I keep getting a compile error. What am I doing wrong?

Bob Phillips · 08-24-2005, 05:05 PM

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)

<[email protected]> wrote in message
news:[email protected]...
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?

Carlos Lozano · 08-24-2005, 06:05 PM

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

"[email protected]" 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?
>
>

Carlos Lozano · 08-24-2005, 06:05 PM

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

"[email protected]" 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?
>
>

Carlos Lozano · 08-24-2005, 06:05 PM

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

"[email protected]" 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?
>
>