Err Msg: Error in Procedure Causes "Cannot Find Procedure" (107469)

SYMPTOMS

In Microsoft Excel, when you open a workbook that contains a defined-name automatic Visual Basic procedure, you may receive the following error message:

Cannot find <procedure>, which has been assigned to run each time [<workbook>]<worksheet> is opened. Continuing could cause errors. Cancel opening [<workbook>]<worksheet>?

where <procedure> is the name of the Visual Basic procedure that has been defined on a worksheet in <workbook> with a name that begins with Auto_Open and <worksheet> is the first worksheet in <workbook>.

CAUSE

CAUSE 1

This error message may be caused by one of the following:

The procedure that is being called cannot be located, as the error message states.

-or-
Although Microsoft Excel does locate the procedure, the procedure contains an error, such as a syntax error.

CAUSE 2

You can also receive the aforementioned error message if you have a defined name "Auto_Open" (referring to Sheet1!A1 for example), without quotes, and a macro in the workbook that is also called "Auto_Open", without quotes.

RESOLUTION

CAUSE 1

If you receive this error message, and you have verified that the procedure does exist in the location specified in the defined name, do the following to view the error in the procedure that is causing the incorrect error message:

In the worksheet that the procedure is defined on, choose Macro from the Tools menu.
From the Macro Name/Reference list, select the procedure that is causing the error, and choose Run.

The real error message appears, making it easier for you to debug the procedure. When you correct the problem with the macro, the "Cannot find..." error message will no longer appear.

CAUSE 2

If you receive the above error message because you have both a defined name called "Auto_Open" and a macro called "Auto_Open," rename the defined name or the macro to something other than "Auto_Open."

MORE INFORMATION

You can create a defined name on a worksheet that refers to a Visual Basic procedure so that it automatically runs that procedure when the worksheet is opened, closed, activated, or deactivated. If you define a name that begins with Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate, and the name refers to a Visual Basic procedure, that procedure will run when the described action is performed on the worksheet.

If the procedure contains an error, such as a syntax error, the error message described above will occur when the procedure is run as a defined- name automatic procedure. The error indicates that the procedure was not found, although the actual cause of the error may be a runtime error in the procedure itself.

NOTE: If the same macro is run as an automatic procedure (the procedure is given the name Auto_Open or Auto_Close) and you do not have a defined name called "Auto_Open", without quotes, the correct error will appear. For more information about creating automatic procedures and using defined-name automatic procedures, see pages 266-268 in the "Visual Basic User's Guide," version 5.0.