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.
Thanks.


WatnogAsked:
Who is Participating?
Advertise Here
 
CluskittConnect With a Mentor Commented:
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
 
RunriggerCommented:
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
 
WatnogAuthor Commented:
This is Excel 2003, I cannot find References under Tools.
I checked and I'm still Admin on this box.   :-]
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Sign Up
 
CluskittCommented:
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
 
RunriggerCommented:
Thanks Cluskitt, I wrongly assumed that the author would have known that!

What did the teacher tell me (Full information), sorry watnog
0
 
WatnogAuthor Commented:
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
 
CluskittCommented:
Possibly some corrupt component, or similar. Try repairing office. If that fails, a uninstall/reinstall should fix it. But repair should be enough.
0
 
WatnogAuthor Commented:
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
 
WatnogAuthor Commented:
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
 
CluskittCommented:
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
 
WatnogAuthor Commented:
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
 
CluskittCommented:
Glad to help :)
0
 
surya tejaCommented:
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
 
WatnogAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.