Link to home
Start Free TrialLog in
Avatar of Watnog
WatnogFlag for Belgium

asked on

Excel VBA Runtime error '5' Invalid procedure call or argument

Driving me crazy....
This works on other pc's but not on mine anymore.
When I open the workbook this used to work flawlessly: a floating toolbar with macros assigned...

Sub Auto_Open()
    Dim cbr As CommandBar, ctl As CommandBarControl
   ' create a new commandbar called 'tester'
    Set cbr = CommandBars.Add("MACROS", , , True)
    ' add a control to it
    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
      .Caption = "Import TransposedJobStream.xls"
      ' assign the macro
      .OnAction = "ONE"
      ' set the button to display text
      .Style = msoButtonCaption
   End With
   ' repeat steps for other controls
    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
      .Caption = "Import exported JSC 'All Calendars' view"
      .OnAction = "TWO"
      .Style = msoButtonCaption
   End With
    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
      .Caption = "XREF"
      .OnAction = "THREE"
      .Style = msoButtonCaption
   End With
    With cbr
      ' create a floating toolbar
      .Position = msoBarFloating
      .Height = 100
      .Width = 50
     ' show the toolbar
      .Visible = True
   End With
   End Sub

Since yesterday I get "Runtime error '5' Invalid procedure call or argument".
The error points to:   Set cbr = CommandBars.Add("MACROS", , , True)   on line 4.
What could suddenly be causing this. Fact is that I have been tinkering on macros for a while but how that could make this stop working is a mystery to me.

I use the same code on other .xlt files but they end similarly in error (whereas before it worked fine).
What can possibly be the cause of this. I've tried closing personal.xls or any other file holding code, rebooted the pc,  .... As said, on other pc this does still work.

Your help will be greatly appreciated.

Avatar of Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

go to Tools - References, do you have any tagged as "MISSING" in the resulting list?

I had something similar happen on my PC, although not the same. It was caused by me losing my "Admin" status on the PC, removed overnight via a script by our Big Brother (IT)
Avatar of Watnog


This is Excel 2003, I cannot find References under Tools.
I checked and I'm still Admin on this box.   :-]
I believe that all versions of excel have tools->references, because the VBA IDE is the same. To clarify, Tools->References in the VBA editor, not in Excel.
Thanks Cluskitt, I wrongly assumed that the author would have known that!

What did the teacher tell me (Full information), sorry watnog
Avatar of Watnog


Sorry. Hello Cluskitt.
I have 4 Ref's tagged:
- Visual Basic for Applications
- Micorsoft Excel 11.0 Object Library
- OLE Automation
- Micorsoft Office 11.0 Object Library

This is as on the PC where this code is working.
Possibly some corrupt component, or similar. Try repairing office. If that fails, a uninstall/reinstall should fix it. But repair should be enough.
Avatar of Watnog


Did a repair, didn't help.
Did a reinstall, didn't help either.

Other macro's run.
If I make other macros "Auto_Open" they run alright too.

If I start the toolbar macro manually I get same error on
    Set cbr = CommandBars.Add("MACROS", , , True)

Is there alternative, more foolproof, code to create a toolbar?
Avatar of Cluskitt
Flag of Portugal image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Watnog


You are my hero Clusskitt.
Yes, it does solve.

Can you explain what happened?
Would I need to change to unique names to be on the safe side?

Basically, you're trying to create a new commandbar called MACROS, but there already exists a commandbar called MACROS. Names have to be unique, ALWAYS. Creating a new control with an existing name will do that.
If it happens on one computer and not the other, it's possible that a manual commandbar with that name has been created. For the code to work like on the other PC, you would have to remove it before creating it.
Avatar of Watnog


Ok, yes I have found it.... View->Toolbars and there it was.
Upon deleting all macro_made toolbar references there I could use the old "MACROS" again.

I checked and upon quitting the file and stopping/starting excel that toolbar was removed automatically. It must have been that mechanism that failed thus causing my problems.
Learned a lot again. Thanks.
Glad to help :)
Avatar of surya teja
surya teja

Hello everybody,

I have been frustrated with the same error "Run-time error '5': Invalid procedure call or argument"

Since last 20days i have been facing this problem. Before that everything was good and well going with MACROS file.
Suddenly one macro file in which few automatic calculation buttons are not working and displaying Microsoft Visual Basic error as described above.

kindly please help me to get out of this.
Sorry, unlike the above "Wantong" i cannot copy my macros code because it is a huge huge huge huge macro file.

I dont know how to resolve it.

Thanks in advance.
Avatar of Watnog


Hi, I got notification of your update on this old question of mine.
If I were you  I would create a new question (you may refer to this one in it).
I assume that's the best and fastest way to get your answer.