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]

Parry: Thanks very much for your suggestions. I tried them all, and here are the results:
---I checked for code in all of the sheets (within the Excel Objects tab in VBA). Result. No code other than "Option Explicit" within Sheet 6
---I deleted all code from "This Workbook" tab
--- I unchecked add ins "Analysis Toolpak" and "...VBA".
---There were no files in folder "XLStart"
--- Within VBA "tools/references" there are only (5) checks....
*VBA, *Object Library, *OLE Automation, *Forms 2.0 Library, *Ref edit Control. None of those seem to point to my troublesome macro.
So I struck out on all accounts. Do you have any other ideas? Thanks.

 

[parry]

mmm. What happens if someone else tries it on their PC? Need to see if its an environment issue (such as the xltart scenraio) or definately that workbook alone.

Worst comes to worse you may need to copy the data (do not move sheets or simply use save as) for each sheet into a new workbook.

 

[wotonka]

I actually received this from another user who was in another office 500 miles away. He said he had the same error message. I put it on a zip disk, and tried it on my home computer. I have a newer version of Excel, and it gave me grief about not being able to run the macros, and requiring a digital signature. I will have to figure that out later.

 

[parry]

The security wasnt something that immediatley came to mind but I wonder if theres some link with your problem. Under Tools|Macro|Security then the Security Level and Trusted Sources tabs what do you have?

 

[wotonka]

Parry,
I may have not made this clear... My home computer is running Office 2003. The other two office computers which experienced the problems w/ the macro are running Office 2000.
The settings on my personal computer, though are:
--- Nothing specifically listed in the "white box" for acceptable sources
--- "Trust all Installed Add Ins and Templates" : Checked
--- "Trust Access to VB Project" : Not Checked.

An important point is that the man 500 miles away (using Office 2000) was able to click on the cell in the "Templt" tab and change values. This happened just a few days age. Only today did he get the error message. Thanks

 

[parry]

Well if it was working before but not now and you not aware of any code changes then that suggests a virus or environment chnage. However it doesnt sound like your typical virus to me but run a virus check anyway to be safe. I have no further ideas - Ive sent you a PM so you can send the book to me if you wish. I might be able to see the issue.

I dont know how old xl4macros worked but they had a separate macro sheet. I assume its not a really old workbook (pre 1997).

 

[tusharm]

{snip}

An important point is that the man 500 miles away (using Office 2000) was able to click on the cell in the "Templt" tab and change values. This happened just a few days age. Only today did he get the error message. Thanks

Does this mean that you cannot edit any cell in the tmplt worksheet? Even after dismissing the error message?

Is the worksheet protected?

Is is covered by an invisible shape? Activate the worksheet. Switch to the VBE. From the Immediate window (CTRL+G), type ?activesheet.shapes.count and then press ENTER. What do you get?

If it is not zero -- and especially if it is one, type activesheet.shapes(1).select press ENTER and switch to the XL window. What's selected?

 

[wotonka]

Thanks, Parry for all of your help.

Tusharm - thanks for your input. ...
Yes, that's correct. I cannot edit any cell within "templt" even after clicking "OK" after the error message.

Eureka!! I un-protected the sheet, and the error message goes away. Although I am glad (it seems to work now), why the error message popped up when the sheet was still protected doesnt make sense. The error message should have been something like "this is read only". Also, the fact that we were able to type text into blank cells just a few days ago doesn't make sense that we are not able to do the same today.

For the last topic, the "count" (within the immediate window) returned the number "7" (is this the number of sheets which are associated w/ macros?)
Thanks for your insight ! With the exception of not understanding the nature of the error message, this spreadsheet may be fixed again ! Thanks

 

[tusharm]

Somewhere there is an active BeforeDoubleClick event procedure. Of course, it could be some other event procedure that is also raised by XL when one tries to edit a cell but double-clicking to edit a cell works in this extremely limited test.

Put the code below in a worksheet module. Ensure that all cells in a worksheet are locked. Then, protect the worksheet with the only selected option being 'select unlocked cells'. You will get performance identical to what you were experiencing (except for my showing a msgbox rather than trying to run a non-existent macro).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox "Boo!"
    Cancel = True
    End Sub

Edit: Ummm...it has to be somewhat more sophisticated than the above because it should not cause any hiccup if the worksheet is not protected. But, you get the idea...

Thanks, Parry for all of your help.

Tusharm - thanks for your input. ...
Yes, that's correct. I cannot edit any cell within "templt" even after clicking "OK" after the error message.

Eureka!! I un-protected the sheet, and the error message goes away. Although I am glad (it seems to work now), why the error message popped up when the sheet was still protected doesnt make sense. The error message should have been something like "this is read only". Also, the fact that we were able to type text into blank cells just a few days ago doesn't make sense that we are not able to do the same today.

For the last topic, the "count" (within the immediate window) returned the number "7" (is this the number of sheets which are associated w/ macros?)
Thanks for your insight ! With the exception of not understanding the nature of the error message, this spreadsheet may be fixed again ! Thanks

 

[Joe Was]

Years ago one of the first problems I had, which sent me to the original Mr. Excel board One, was:

The same problem, I had played with Linking workbook changed some settings and from then on every time I saved a Workbook when another was open at the same time. Links got set automatically messing every thing up.

You may have some link set without you knowing about it?

Back then I downloaded a link-breaker and changed some settings [I do not remember which?] and deleted files so badly linked they could not be saved and the problem went a way.