Help Adding a Menu to an Excel Workbook

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????
TammiAsked:
Who is Participating?
 
Richie_SimonettiConnect With a Mentor IT OperationsCommented:
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
 
AndrewDevCommented:
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
 
DanRollinsCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AndrewDevCommented:
Fine by me
Andrew
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0
 
Richie_SimonettiIT OperationsCommented:
Thanks to all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.