Macro Not Found...

[wotonka]

I have an existing spreadsheet which has a worksheet (called tmplt) with an area used as a template for other worksheets (tabs 1000, 2000). When in tab 1000 for example, a macro opens the tmplt tab, copies the templat area, and pastes it (along w/ formulas) into the 1000 tab. It works fine. The problem is, when I click on the "tmplt" tab, and click anywhere in the area of that sheet, an error appears "The macro 'Schedule.xls'!AddLine' cannot be found". The error message is correct, there is no macro with the referenced title, but why would just clicking within any area of the tab "tmplt" cause a macro (which happens to not exist) to try to execute? I inherited this spreadsheet, so maybe there is an auto-execute function somewhere??

Thanks for any help you may offer. It is most appreciated !

 

[wotonka]

If I disable macros upon opening, then I can click on any worksheet, and no error messages. However, when I click on worksheet "Tmplt", and click anywhere on the sheet, the following message appears: "The macros in this project are disabled. Please refer to online help or documentation of the host application to determine how to enable macros"
Several things are strange about this:
1. If you click anywhere on the "Templt" tab, the macro tries to execute.
2. Just a couple days ago, I edited the cells within the "Templt" tab (by typing text only within 3 of the cells)
3. I dont find any references to the macro that this is trying to call anywhere in the code (although the macro it is trying to find may have been there at one time).

The macro codes were written post year 2001. Thanks.

 

[starl]

what if you rename the sheet?
and did you try zooming out?

 

[wotonka]

I re-named the sheet to "test". I zoomed out from 80% to 100%, but unfortunately, clicking anywhere results in the same error message:
"The macro "Schedule.xls'!AddLineVB'cannot be found". Do you have any other suggestions? I appreciate your help.

 

[starl]

so..it's not based on the name of the sheet
it's active sheet - but only this sheet..

When I said zoom out - I meant to zoom to like 25% - so you can see as much of the sheet as possible - to see if there are any areas you don't usuallylook at.

can you take the workbook home? could we set up a desktop sharing session and you can show me the workbook?

 

[wotonka]

Thanks. I zoomed to 25%, and didn't see anything unusual.
I work out of my home office (so I am really "home", now). I found out that the company disallows anyone to net meeting in - because it poses a security risk to our intranet. Again, here you raise the ladder to my burning building, and I say "no thanks" (well, my company says no thanks). I am sorry, but grateful for the offer.

 

[starl]

what if you copy the sheet to another workbook? Does the code still trigger?

 

[wotonka]

No, If I hit Ctrl A (select entire worksheet "Tmplt") , copy, then paste in to a blank sheet of a new workbook, the code does NOT trigger.

 

[starl]

no - I mean actually copy/move the sheet to another workbook. You do this by right-clicking on the tab

 

[wotonka]

Thanks for the clarification. When I copy the "Templt" tab to another workbook, then trying to click on any cell DOES result in the same error message "The macro "Schedule.xls'!AddLineVB'cannot be found".

 

[parry]

If I disable macros upon opening, then I can click on any worksheet, and no error messages. However, when I click on worksheet "Tmplt", and click anywhere on the sheet, the following message appears: "The macros in this project are disabled. Please refer to online help or documentation of the host application to determine how to enable macros"
Several things are strange about this:
1. If you click anywhere on the "Templt" tab, the macro tries to execute.
2. Just a couple days ago, I edited the cells within the "Templt" tab (by typing text only within 3 of the cells)
3. I dont find any references to the macro that this is trying to call anywhere in the code (although the macro it is trying to find may have been there at one time).

The macro codes were written post year 2001. Thanks.

Hi, so when you click on the sheet tab (ie your in another sheet and you click on the sheet tab Templt) and code is executed. This is telling me that a Worksheet_Activate event is run presuming this code isnt also run when you click on any other tab. An event can be run by having code within the sheet module in the workbook concerned or as a workbook level Worksheet_Activate event (ie it runs whenever you click on any tab but only does actions when it sees the name of the tab is Templt). The workbook level code may be in the ThisWorkbook module or a standard module (Module1, Module2 etc). It may even be in an Addin or in a workbook that is started when Excel starts (ie it is located in the XLStart folder where Excel has been installed). If theres a class module there might be someother application event in play as well.

So this is really a process of elimination.
1) Check every object within the project (ie in the VBE)
2) Uncheck all Addins (Tools|Addins) then restrat the book and see if it goes away. If so, then one by one check each addin to see which is the culprit.
3) Through Windows Explorer, look to see if there is a workbook in the xlstart folder - either under your login under Documents & Settings or whever Excel was installed. If there is a workbook, move it to your desktop then start Excel and see if the problem has gone away.
4) In the VBE, select Tools|References and see if there is any reference to the Schedule workbook its trying to find the code in. If so, uncheck the box and close then start Excel again and see if the problem has gone away.

hth

EDIT: Forgot to mention that the xlstart folder is a hidden folder. Therefore in Windows Explorer look under Tools|Folder Options and in the View tab ensure that the option "Show Hidden Files and Folders" is selected.