INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

"Method or data member not found" on closing Excel

"Method or data member not found" on closing Excel

(OP)
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.

RE: "Method or data member not found" on closing Excel

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

RE: "Method or data member not found" on closing Excel

(OP)
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

RE: "Method or data member not found" on closing Excel

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

RE: "Method or data member not found" on closing Excel

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:
  1. back up the code
  2. delete the activex control
  3. recreate the activex control
  4. test closing excel without adding code to the new control
  5. test closing excel with some very basic code such as "dim x as integer: x = 1 + 1" or something
  6. If that works fine, paste back in your original code over the test code, and test closing excel with that.

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

RE: "Method or data member not found" on closing Excel

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

RE: "Method or data member not found" on closing Excel

(OP)
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 "Private sub cboHospital-change()", 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

RE: "Method or data member not found" on closing Excel

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close