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(msoContro
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(msoContro
With ctl
.Caption = "Import exported JSC 'All Calendars' view"
.OnAction = "TWO"
.Style = msoButtonCaption
End With
Set ctl = cbr.Controls.Add(msoContro
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.
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(msoContro
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(msoContro
With ctl
.Caption = "Import exported JSC 'All Calendars' view"
.OnAction = "TWO"
.Style = msoButtonCaption
End With
Set ctl = cbr.Controls.Add(msoContro
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.
ASKER
This is Excel 2003, I cannot find References under Tools.
I checked and I'm still Admin on this box. :-]
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
What did the teacher tell me (Full information), sorry watnog
ASKER
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
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.
ASKER
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.
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 :)
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.
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.
ASKER
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.
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.
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)