Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile error: Method or data member not found | Excel Worksheet Functions | |||
Compile Error Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming |