Solved

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

Posted on 2010-09-03
14
6,252 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

15 Experts available now in Live!

Get 1:1 Help Now