Compile error 'Method or data member not found' when closing Excel
in [Programming]
|
Prev: Pivot Tables
Next: Solver in VBA
From: Flemming on 30 Jan 2007 16:22 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
From: Tom Ogilvy on 30 Jan 2007 16:47 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 > > >
From: Flemming on 30 Jan 2007 17:05 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message news:9E94211E-EC8F-4DFB-9F92-433D37C76CD9(a)microsoft.com... > 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 >> >> >>
From: Tom Ogilvy on 30 Jan 2007 18:54 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" <dont(a)spam.me> wrote in message news:ucya3qLRHHA.3520(a)TK2MSFTNGP05.phx.gbl... > 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message > news:9E94211E-EC8F-4DFB-9F92-433D37C76CD9(a)microsoft.com... >> 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 >>> >>> >>> > >
From: Flemming on 31 Jan 2007 11:45
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" <twogilvy(a)msn.com> wrote in message news:OCFQqnMRHHA.3500(a)TK2MSFTNGP05.phx.gbl... > 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" <dont(a)spam.me> wrote in message > news:ucya3qLRHHA.3520(a)TK2MSFTNGP05.phx.gbl... >> 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message >> news:9E94211E-EC8F-4DFB-9F92-433D37C76CD9(a)microsoft.com... >>> 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 >>>> >>>> >>>> >> >> > > |