Run-time error '5': Invalid procdre call or argumnt - End/Debug/He

[akm]

When exit Excel, get 'Microsoft Visual Basic' pop-up error message ...
"Run-time error '5': Invalid procedure call or argument", with buttons 'End',
'Debug', and 'Help' to click.

When click 'End', the program ends and all seems ok.

Why does it need to go thru this process, and is there a problem that we are
missing ?

 

[Joel]

the is a macro whih gets executed automatically when you close the workbook.
If you press debug you will see the macro. You can copy and post the code if
you want help getting rid of the error.

Yo can also delete the macro if you want.

 

[akm]

Joel
Thank you for the reply!
The code is as follows...

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' remove item from menu
Application.CommandBars("Worksheet Menu
Bar").Controls("&Tools").Controls("Custom Delimited File") _
.Delete

End Sub

Private Sub Workbook_Open()

Dim cb As CommandBar
Dim cbp As CommandBarPopup
Dim cbb As CommandBarButton

' add button under Tools to the Excel menu to execute the add-in

Set cb = Application.CommandBars("Worksheet Menu Bar")
Set cbp = cb.Controls("&Tools")
Set cbb = cbp.Controls.Add(Type:=msoControlButton, Temporary:=True)
With cbb
.Caption = "Custom Delimited File"
.BeginGroup = True
.OnAction = "MakeFile"
On Error Resume Next
.FaceId = 3272
On Error GoTo 0
End With

Set cbp = Nothing
Set cbb = Nothing
Set cb = Nothing

End Sub

 

[Jacob Skaria]

--To handle the error add one more line in Private Sub Workbook_BeforeClose
as below...

--Mean while is there a additional button getting added under the Tools menu
named 'Custom Delimited File'


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next

' remove item from menu
Application.CommandBars("Worksheet Menu
Bar").Controls("&Tools").Controls("Custom Delimited File") _
.Delete
End Sub

If this post helps click Yes

 

[Joel]

What version of excel are you using? Do you specify allow macros to run
whenyou open the workbook?

The macro you psoted is deleting a toolbar when the workbook is closed. The
toolbar was added by the workbook opn event that you posted. the workbook
open event has an On Error statement that is probably eliminating the error
when the workbook is opened. If you are not using the command bar then I
would eliminate all the code and save the workbook. It is silly to have code
in a spreadsheet that you never use.