Solved

Help Adding a Menu to an Excel Workbook

Posted on 2001-07-31
6
717 Views
Last Modified: 2012-08-14
I am using the following code to add a menu to an Excel Workbook.  This is called from the AutoOpen Sub :

Public Sub AddPFMUpdateMenu()
    Dim myMenuBar, msoControlPopup, newMenu, msoControlButton, ctrl1 As Variant
    Dim msoButtonCaption As Variant
   
    Set myMenuBar = CommandBars.ActiveMenuBar
    Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
        newMenu.Caption = "&PFM Update"
    Set ctrl1 = newMenu.Controls.Add(Type:=msoControlButton, Id:=1)
    ctrl1.Caption = "&Update Worksheet with PFM Information"
    ctrl1.TooltipText = "PFM Update"
    ctrl1.Style = msoButtonCaption
End Sub

I keep getting an Runtime error 5 "Invalid procedure call or argument" when it hits the second line of code.   I can't figure out why I keep getting this error.  I use the exact code on other workbooks without a problem.  This particular workbook has multiple worksheets,
but I don't think that should make a difference.

Any ideas????
0
Comment
Question by:Tammi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6338189
This is not an exact fit but maybe close. When you automate Microsoft Excel, if you call the Add method of the QueryTables collection and supply an ADO recordset for the Connection argument, you receive the following error message at run time:

Run-time Error '5':
Invalid procedure call or argument

This is the full article including work around
http://support.microsoft.com/support/kb/articles/Q263/4/98.ASP

Hope this helps
Andrew
0
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 50 total points
ID: 6338581
Well, this example is spanish and add more than one menu.
Check this out to see how it works and modify accordingly. Good luck

Sub CrearMenu()

Dim oMiBarra As CommandBar
Dim oEmergente As CommandBarPopup
Dim oEmergente3 As CommandBarPopup
Dim oEmergente4 As CommandBarPopup
Dim oCascada As CommandBarPopup
Dim oCascada2 As CommandBarPopup
Dim oCascada5 As CommandBarPopup
Dim oCascada6 As CommandBarPopup


'OcultarMenu


Set oMiBarra = Application.CommandBars.ActiveMenuBar '.Add(Name:="REGISX", _
Position:=msoBarTop, MenuBar:=True)
Application.ShowToolTips = True

    With oMiBarra.Controls
        Set oEmergente = .Add(msoControlPopup, , , 1)
        With oEmergente
            .Caption = "&Registro"
            With .Controls.Add(msoControlButton)
                .Caption = "&Nuevo"
                .OnAction = "NuevoInforme"
            End With
        End With
        Set oCascada = oEmergente.Controls.Add(msoControlPopup)
        With oCascada
            .Caption = "Ordenar"
            With .Controls.Add(msoControlButton)
                .Caption = "Por &Apellido"
                .OnAction = "OrdenPorApellido"
            End With
            With .Controls.Add(msoControlButton)
                .Caption = "Por &Dias de Dialisis"
                .OnAction = "OrdenPorDias"
            End With
            With .Controls.Add(msoControlButton)
                .Caption = "Por &Obra Social"
                .OnAction = "OrdenPorObraSocial"
            End With
        End With
        Set oCascada2 = oEmergente.Controls.Add(msoControlPopup)
        With oCascada2
            .Caption = "Imprimir"
            With .Controls.Add(msoControlButton)
                .Caption = "&Registro X"
                .OnAction = "ImprimeRegX"
            End With
            With .Controls.Add(msoControlButton)
                .Caption = "&Reporte Operativo"
                .OnAction = "ImprimeEstadisticas"
            End With
            With .Controls.Add(msoControlButton)
                .Caption = "&Ambos Reportes"
                .OnAction = "ImprimeTodo"
            End With
        End With
        Set oCascada5 = oEmergente.Controls.Add(msoControlPopup)
        With oCascada5
            .Caption = "Enviar a"
            With .Controls.Add(msoControlButton)
                .Caption = "&Disco de 3? (A:)"
                .OnAction = "BajadaDiskette"
            End With
            Set oCascada6 = oCascada5.Controls.Add(msoControlPopup)
                With oCascada6
                    .Caption = "Destinatario de E-Mail"
                    With .Controls.Add(msoControlButton)
                        .Caption = "&Informe de Cierre Mensual"
                        .OnAction = "EnvioTodos"
                    End With
                End With
        End With
        With oEmergente.Controls.Add(msoControlButton)
            .Caption = "&Salir"
            .OnAction = "Salir"
        End With
    End With
    With oMiBarra.Controls
        Set oEmergente3 = .Add(msoControlPopup, , , 2)
        With oEmergente3
            .Caption = "&Ver"
            With .Controls.Add(msoControlButton)
                .Caption = "&Historial de Crecimiento"
                .OnAction = "VerHistorial"
            End With
        End With
        Set oEmergente4 = .Add(msoControlPopup, , , 3)
        With oEmergente4
            .Caption = " &? "
            With .Controls.Add(msoControlButton)
                .Caption = "&Ayuda de Registros X"
                .OnAction = "MuestraAyudante"
            End With
            With .Controls.Add(msoControlButton)
                .Caption = "A&cerca de Registros X"
                .OnAction = "MuestraAcerca"
            End With
        End With
    End With
    oMiBarra.Protection = msoBarNoMove + msoBarNoCustomize
    oMiBarra.Visible = True
End Sub

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7198294
Hi Tammi,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Richie_Simonetti's comment(s) as an answer.

Tammi, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:AndrewDev
ID: 7202042
Fine by me
Andrew
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7213203
Per recommendation, force-accepted.

Netminder
CS Moderator
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7216076
Thanks to all.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question