F9 Run Time Error 1004

[nancymac623]

I'm sure this is a no brainer for some, but I'm quite sick of getting this message when I calculate in some spreadsheets by simply clicking F9. I know a little about VBA and play around with it, but since it's a default macro, I'm not sure where to go or what to do to fix it.

Thanks for any help....

More info, the exact message for "Run Time Error 1004" is
"Unable to get the_NewEnum property of the OLEobjects class"

 

[Datsmart]

If you are getting this error in only some workbooks, then you must have some improper formulae in them.
Can you confirm that you get the error in only certain workbooks?

 

[nancymac623]

Yes, it is only some workbooks, but the number of files with the problem is increasing. That's the reason it's getting so annoying! :x

 

[Datsmart]

Can you email me one of the errant workbooks?
That way I can see if it errors on my PC and also check your formulae.

 

[nancymac623]

No, I'm sorry. I tried "sanitizing" it by deleting sheets to allow it out of the company, but then the error message disappeared. That tells me for sure it's something in one of my worksheets causing the probem. I went to every sheet and calculated just that sheet using Shift F9, but I didn't get the message that way either. I only get the message when calcing the entire file.

Is there a systematic way of searching for the worksheet with the problem?

 

[Datsmart]

Delete those sheets one at a time and check using F9 as you delete them.
Once you find the bad sheet, check all formulae closely.

 

[nancymac623]

Thanks, I'll try that tomorrow.

Once I find the sheet causing the problem, I'll check the formulas. Could just any formula cause the problem, or it is likely a a particular type?

 

[Datsmart]

Since I can not repeat the error, I don't know if it is a formula or maybe WorkSheet event code causing the error.
Do you have any event procedures taking place?
Right Click the Sheet Tab, click View Code...
If the VBA editor page that opens is blank, you have no worksheet event code.
You can also check for WorkBook event code.
Right Click the Excel Icon to the left of the File Menu, click View Code...
If the VBA editor page that opens is blank, you have no workbook event code.