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

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

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" <(E-Mail Removed)> wrote in message
news:9E94211E-EC8F-4DFB-9F92-(E-Mail Removed)...
> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9E94211E-EC8F-4DFB-9F92-(E-Mail Removed)...
>> 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
>>>
>>>
>>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:9E94211E-EC8F-4DFB-9F92-(E-Mail Removed)...
>>> 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
>>>>
>>>>
>>>>
>>
>>
>
>

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" <d...@spam.me> 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.

*What* problem and *what* error ?

Tim

<Ben Milroy> wrote in message news:(E-Mail Removed)...
>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?