Compile error 'Method or data member not found' when closing Excel

[Flemming]

Hi all,

I have an Excel file that works fin when I open, use or close it... BUT....

If I open the file and close the whole Excel application (not only the
file)... then I get Compile error "Method or data member not found"
The debugger focus on the name of combobox that exist..!

Any idea???
Excel 2007

Thanks,
Flemming

 

[Tom Ogilvy]

closing a workbook can cause a recalculation. If you have a click event in
this combobox, then that may be the source of the error. I sometime unlink
these controls so they are not affected by recalcs and manage all their
activities with code.

A can't explain the disparity in the fact you say in only happens when you
close excel.

 

[Flemming]

Hi Tom
You again nice

I only have change events. I have vba-named the sheet to 'wksHyboxDiagram'
and have lots of sheet controls like 'cboNG1_1'... well it stops on this
control name.... or the next if outcomment this one.

There is no issue closing the workbook - but ONLY when I close the Excel
application with the workbook open.
I have not BeforeClose or AutoClose procedures - no macro should run on
close.

---start snip---
Private Sub cboHYFCNG6_Change()
On Error Resume Next
Application.ScreenUpdating = False
Dim bTemp As Boolean
If wksHyboxDiagram.cboHYFCNG6.ListIndex = 0 Then
wksHyboxDiagram.cboNG1_1.Visible = False
---end snip---

I even can not stop the debugger unless I outcomment the hole
sheet-module-code...


Cheers,
Flemming

 

[Tom Ogilvy]

You have a change event for your control. If your combobox is linked to a
cell in the worksheet, the closing of the sheet can cause your change event
to fire when the sheet is calculated. Maybe this isn't your problem, but as
I said, I have seen it cause problems.

 

[Flemming]

Hi Tom

(I have change events and controls linked to cells)

When I closing the application it seems like Excel says that the control
object does not exist.

It seems like Excel closes the workbook before it runs the last event code,
and therefore the control object can not be found... it is strange.

Cheers,
Flemming

 

[AnandaSim]

I have had the same problem. Excel 2003. Drop Down Box on a worksheet
with an OnChange event which is supposed to refer to another Drop Down
Box on the same worksheet. After reading this thread, resolved the
compile-time error by moving all code to the OnClick event of the
first Drop Down instead of the OnChange event.

Thanks for bringing the matter up.

Ananda

 

[Tim Williams]

*What* problem and *what* error ?

Tim