"Method or data member not found" on closing Excel

[kndavies]

Hi,
Apologies if this has been answered somewhere but the only other instance of this question I found had no responses, I'm hoping to be more lucky!

I have a workbook with several Active X controls on one sheet, if I close the workbook there is no problem but closing Excel (if there are no other workbooks open) throws the 'Method or data member not found' and highlights one of the combobox_onchange events. This procedure references other controls which appear to have been removed already as Excel quits and leads to the error. Commenting this procedure out shows that no other controls are generating the error.

Can I somehow force the controls to be discarded in a particular order so that the referenced controls are still in place when the combobox is discarded? Are there any other techniques I can try to overcome this? Any and all suggestions gratefully received, thank you.

 

[combo]

Analyse why this event is firing, probably you have a chain of events that try to modify closed objects. Either redesign your code to avoid this situation or test if you can access this object before using it.

combo

 

[kndavies]

Hi Combo,

Thank you for your response, I have been trying to find what is 'firing' the event but I'm not sure it is firing. The error message is a compile error and with no linked cells for any of the controls and no events associated with the workbook closing I am baffled.

Why the workbook would shut as expected with File/close but throw this error when the application is shut without shutting the workbook first is beyond me. It looks as though I am destined to join the group of people who have reported this issue for 10 years or more but never found a solution...

Ken

 

[kjv1611]

So you said if you comment out the event, you get no error? What's the code that's highlighted with the error? As far as why it's firing in the first place, you could search your the project for the combo box by name to see if something else is for some reason triggering the procedure. Also, double check and make sure there is no code under "Workbook_Close" and related events.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

If you cannot find a real solution, then there are ways to modify the code in the VBE from VBA, though I honestly forget how to do that as it's pretty rare you'd need to do so. In this case, I could see commenting out the code that throws the error with the workbook_close event, and then uncommenting the code with the workbook_open event.

One other idea:
[ol 1]
[li]back up the code[/li]
[li]delete the activex control[/li]
[li]recreate the activex control[/li]
[li]test closing excel without adding code to the new control[/li]
[li]test closing excel with some very basic code such as "dim x as integer: x = 1 + 1" or something[/li]
[li]If that works fine, paste back in your original code over the test code, and test closing excel with that.[/li]
[/ol]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

Compile your vba project. If you can do it, the problem is linked with referencing the object you closed.
If the vba project is not password protected, you can set VBE general options to "break on all errors" or "break in class modules", this will allow to break in specific line that raises error.
Is the code in regular workbook or in add-in?

combo

 

[kndavies]

Hi Combo again and kjv1611,

If you don't mind I'll answer both your questions and suggestions in the one post.

The code that's highlighted is "[highlight #FCE94F]Private sub cboHospital-change()[/highlight]", this event leads to other controls being populated with the existing data for the selected hospital but as they need to be editable the other controls are comboboxes. At this point typing "me." in the procedure shows that these controls are no longer in existence.

No other procedures refer back to this combo box and there are no actions under the workbook.close event.

Modifying the code itself with vba seems extreme, but an interesting approach, I'll look at that and also your sequence for replacing/testing the control. I have tried deleting and remaking it but not with the complete steps you suggest.

Compiling the project shows no issues and I have to protect this project for the client. It is in a workbook, not an add-in.

Thank you both for your thoughts and suggestions, I appreciate them very much.

Ken

 

[combo]

So you have the answer. You shouldn't delete controls that have event procedures. You could hide them instead; anyway, it looks like you need more control in event procedures, controls could fire a sequence of events in built-in order. For debugging there may be a line of code with debug.print and procedure name plus sensitive parameters.

I do not recommend self-modifying code. Among other issues, the project cannot be password protected fof this.
The basic steps in solving this problem, in my opinion, are:
- separate part of vba application and data (don't delete sheets with code, don't delete controls with code), if possible, use master workbook and processed workbooks,
- add error handlong,
- be careful with control events procedures, sometimes a change can cause a chain of not controlled events,
- probably you know, but Application.EnableEventsapplies only to excel objects, activex controls ignore this setting.

combo