Macro Not Found...

wotonka

Board Regular
Joined
Aug 6, 2002
Messages
142
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 !
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
Take a look at the macros in the VB Editor, specifically look in the Sheet modules for a macro that begins:
Code:
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

Board Regular
Joined
Aug 6, 2002
Messages
142
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
What code do you find under this module? Can you post it here?

Is there any code under the ThisWorkbook module also?
 

wotonka

Board Regular
Joined
Aug 6, 2002
Messages
142

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 6, 2002
Messages
142

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
Are any of the macros necessary, or can you remove all the macros? I would remove all unnecessary VBA code.
 

wotonka

Board Regular
Joined
Aug 6, 2002
Messages
142
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,458
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top