Solved

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

Posted on 2010-09-03
14
6,022 Views
Last Modified: 2016-09-07
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.
Thanks.


0
Comment
Question by:Watnog
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 33594719
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)
0
 

Author Comment

by:Watnog
ID: 33594764
This is Excel 2003, I cannot find References under Tools.
I checked and I'm still Admin on this box.   :-]
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33594769
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.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 33594793
Thanks Cluskitt, I wrongly assumed that the author would have known that!

What did the teacher tell me (Full information), sorry watnog
0
 

Author Comment

by:Watnog
ID: 33594803
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.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33594807
Possibly some corrupt component, or similar. Try repairing office. If that fails, a uninstall/reinstall should fix it. But repair should be enough.
0
 

Author Comment

by:Watnog
ID: 33595241
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?
0
Free Trending Threat Insights Every Day

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.

 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 total points
ID: 33595297
Ok, I have found a possible solution: The command bar is already created (and you're trying to create it again). Try to change that line to MACROS1 and see if that fixes it.
0
 

Author Comment

by:Watnog
ID: 33595356
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?

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33595374
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.
0
 

Author Comment

by:Watnog
ID: 33595444
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.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33595782
Glad to help :)
0
 

Expert Comment

by:surya teja
ID: 41788950
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.
0
 

Author Comment

by:Watnog
ID: 41789100
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.

Cheers,
W.
0

Featured Post

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

Suggested Solutions

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I comeā€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

16 Experts available now in Live!

Get 1:1 Help Now