Solved

Help Adding a Menu to an Excel Workbook

Posted on 2001-07-31
6
711 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

821 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