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

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

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

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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

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

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

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

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.

--
Regards,
Tom Ogilvy


"Flemming" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

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

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



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Flemming" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Flemming" wrote:

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

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

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

On Feb 1, 3:45 am, "Flemming" wrote:

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 Iclosethe Excel
application with the workbook open.
I have not BeforeClose or AutoClose procedures - no macro should run on
close.

Sorry to rehash an old problem
 

*What* problem and *what* error ?

Tim

<Ben Milroy wrote in message ...
I have this exact problem, but I need to use the OnChange event for a
worksheet to set the value of a text box. Upon close, I get this error.
Any solutions without changing to OnClick?