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 !

 

[Joe4]

Take a look at the macros in the VB Editor, specifically look in the Sheet modules for a macro that begins:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

This is a macro that is triggered whenever a cell is selected on that worksheet. I suspect that may be the culprit.

 

[wotonka]

Thank you so much for your reply. I found the following code within the "This Workbook" sub folder:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

End Sub


This is the only place such similar code appears. The code does not appear to do much ( I dont understand what it does??). My question is: Why would this code cause the error message "The macro 'Schedule.xls'!AddLine' cannot be found". to appear? I cant find where a macro "AddLine" is called anywhere. I cant find a macro named "AddLine" (consistent w/ the error message)??? Thanks very much.

 

[Joe4]

What code do you find under this module? Can you post it here?

Is there any code under the ThisWorkbook module also?

 

[wotonka]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False 'Turn off screen for faster response
Application.DisplayAlerts = False

'The following code updates the Backup sheet or creates one if it is not already done
'This code is disabled as of 10/25/03 due to lockup problems associated with
'the backup sheet routine.
'If SheetExists("Work") Then 'If there is a sheet entitled "Work"
'If SheetExists("Backup") Then 'If there is a sheet entitled "Backup"
'Sheets("Backup").Delete 'Delete it, we'll make a new one anyway
'End If

'Sheets("Work").Select 'Select sheet "Work"
'Sheets("Work").Copy After:=Sheets(1) 'Make a copy and put it behind current "Work
'Sheets("Work (2)").Name = "Backup" 'Rename the sheet "Backup"
'Sheets("Backup").Select 'Select the sheet "Backup"

'The following line of code protects the backup sheet from changes being made

'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

'Else 'If there is not a "Work" sheet
'MsgBox ("YOU SHOULD MAKE A BACKUP COPY. HIT THE RECOVER BUTTON AND THEN TRY AGAIN.")
'GoTo LeaveSub
'End If

'Sheets("Work").Select 'Select the "Work" sheet to continue with the program

'This is the end of the code that will either update or create a backup copy
This is the code within "ThisWorkbook". Much of it has been commented out:

LeaveSub:
Application.ScreenUpdating = True 'Turn off screen for faster response
Application.DisplayAlerts = True

End Sub

'Private Sub Workbook_Open()

'End Sub

'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

'End Sub

 

[Joe4]

It pretty much looks like all the important stuff of that code is commented out.

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".

When this error occurs, does it give you an option to "Debug"? If so, selecting this option should take you to the code that is trying to call this macro. Then you may be able to see what is going on.

 

[wotonka]

No, there is not an opportunity to debug. The reason the code is commented out, is because when it was enabled, it used to produce an error that I didn't have time to troubleshoot. The functionality of this macro was not critical to the macro, so I just commented it out.

 

[Joe4]

Are any of the macros necessary, or can you remove all the macros? I would remove all unnecessary VBA code.

 

[wotonka]

I removed all of the code from "This Workbook" tab. I saved the VBA screen, and re-tested. Unfortunately, the same error appeared:

"The macro 'Schedule.xls'!AddLine' cannot be found".

I searched for "Add Line" within the VBA editor and it couldn't find it (I tested the search on another known string, and it found it - hopefully proving the search works)

I just cant figure out what is calling the macro of this name??? Are there any other obscure places where macro code could appear (like in "This Workbook")?

 

[Joe4]

The places to check are in the individual Sheet modules and the ThisWorkbook modules. This is where all the automatically triggered macros are stored.

Two things to consider:

-You may have a triggered macro that triggers another macro. For example, you may have a macro that is triggered upon the selection of a sheet (like the one you posted). However, that macro may also select another sheet. And if that other sheet being selected also has code which is triggered upon selection, that code will run also. So you really need to try to follow the code all the way through and try to find out what is going on.

-It is possible to have code in the Personal workbook which is being triggered. Int he VB editor, check to see if you have VBAProject named "Personal.xls" which might be calling some code.