Solved

Help Adding a Menu to an Excel Workbook

Posted on 2001-07-31
6
704 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:AndrewDev
Comment Utility
Fine by me
Andrew
0
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Thanks to all.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now