IN VBA unable to call subroutine in Sheet from Workbook event handlers Workbook_BeforeClose or Workbook_Deactivate()

Question

I'm making an Excel VBA enabled Workbook(RES Project Perspective) has a main table/sheet (RES Project Sheet) from which there's a hyperlink on each row to open another workbook(0520-077-LACOFD-FJF-log.xlsm) with Sheet1(Project Log) which is also VBA enabled. When workbook(..log.xlsm) with Sheet1(Project Log) the secondary table is filled out, I want some of those changes to be updated in the main table (RES Project Sheet).

The update subroutine UpdateMainProjectTable is in Sheet1(Project Log). It work when called from Worksheet_changed event, but when I try to call the update subroutine from the workbook events Workbook_BeforeClose(Cancel As Boolean) or Workbook_Deactivate(), I get the compile error "Sub or Function not defined". Figuring it is a scope error I also tried naming the subroutine Sheet1.UpdateMainProjectTable, but then I get "runtime error 1004 Application Defined or object defined error".

Below are four examples of event handlers used in the Workbook. I tried each one and they each produced an error when trying to call UpdateMainProjectTable. In the first two exThey are only together for reference. Also I included a picture of the project structure and the errors for referene.

The two subroutines below are in the Woorkbook, so I prefix Sheet1. On both when they try to call Sheet1.UpdateMainProjectTable I received "runtime error 1004 Application Defined or object defined error".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
Application.EnableEvents = False
Sheet1.UpdateMainProjectTable
Application.EnableEvents = True
End Sub


Private Sub Workbook_Deactivate()
Application.EnableEvents = False
Sheet1.UpdateMainProjectTable
Application.EnableEvents = True
End Sub

The two subroutines below are in the Woorkbook, and I don't prefix the subroutine. On both when they try to call UpdateMainProjectTable I received compile error "Sub or Function not defined".

Private Sub Workbook_Deactivate()    
 Application.EnableEvents = False
UpdateMainProjectTable
Application.EnableEvents = True    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Me.Saved = False Then Me.Save
 Application.EnableEvents = False
UpdateMainProjectTable
Application.EnableEvents = True    
End Sub

Per the suggestion of an answer I tried this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
Application.EnableEvents = False
With Sheet1
Call UpdateMainProjectTable
End With
Application.EnableEvents = True
End Sub

But I get the "Compile Error Sub or Funtion not defined". Below is my subroutine inside of Sheet1(Project Log) which is declared Public.

Public Sub UpdateMainProjectTable()    'This is the Main function to update the main table when the log is updated
                                'it open's the RES Project table if not open and

Dim fileName As String
Dim projId As String
Dim logEntry As String
Dim waitingNotCleared As Boolean
Dim rowWithMatch As Integer
Dim wbOpenOnEntry As Boolean
Dim wb As Workbook

'gets Projectlog filename--  this filename
fileName = ActiveWorkbook.Name

'check if RES Project Perspective.xlsm is open.  If not then open and close later at end of sub
wbOpenOnEntry = CheckIfWBOpen("RES Project Perspective.xlsm")
        If wbOpenOnEntry = False Then
            Set wb = Workbooks.Open(ActiveWorkbook.Path & "/../../RES Project Perspective.xlsm")
        End If

'uses filename to determine projid
projId = ExtractProjectIDFromFilename(fileName)
Debug.Print "projId = ExtractProjectIDFromFilename(fileName)   " & projId
'rowWithMatch = FindRowWithProjIDMatch(projId)
rowWithMatch = GetMatchRowNumber(projId)
Debug.Print "rowWithMatch = GetMatchRowNumber(projId)  " & rowWithMatch
MakeLogEntry (rowWithMatch)

'check if RES Project Perspective.xlsm is open.  If not then open and close later at end of sub
        If wbOpenOnEntry = False Then
            wb.Close savechanges:=True
        End If
End Sub

I hope this helps!

user_2233308 · Answer

In order to call a Sub/Function from a sheet module, it must not be Private and you must explicitly call it.

Sheet1.UpdateMainProjectTable

If your procedure is not Private and exists in Sheet1, the problem should be inside the called Sub (less probable, I think).

I feel the need to also emphasize that Sheet1 is not the sheet name! It is the sheet module name. I mean, when looking in VBE at your sheet modules you can see something like this: Sheet1 (Your sheet name). If Sheet1 is the name between parenthesis, it would be wrong... Except the case when both are the same.

Edited: I can see (now) your sub. Discussing about Workbook_Deactivate event call:

fileName = ActiveWorkbook.Name does not make the same sense, because after deactivation, another workbook is active. So, try using fileName = ThisWorkbook.Name instead. Do the same for Workbooks.Open(ActiveWorkbook.Path & ..., transforming it in Workbooks.Open(ThisWorkbook.Path & ....

It would be good to also post CheckIfWBOpen function code, or at least, check if something similar does not exist inside its code.

ExtractProjectIDFromFilename is called using a wrong workbook name (the active workbook one). I do not know what other references may also exist in its code.

Since there are also some other functions calls (GetMatchRowNumber, MakeLogEntry) these procedures must be also analyzed from similar points of view.

Now, I would suggest you to try debugging the call in this event in the next way:

Change ActiveWorkbook with ThisWorkbook as suggested;
Put a break Point on the specific line and then press F8 to run the code line by line, when code stops after deactivation and see at which line it raises the error...

user_11167163 · Answer

Be sure that your sub is Public :

Public Sub UpdateMainProjectTable()

End Sub

And to Call this sub just do :

With Sheet1
    Call UpdateMainProjectTable
End With

So your code should be as follow :

Private Sub Workbook_Deactivate()    
Application.EnableEvents = False
With Sheet1
    Call UpdateMainProjectTable
End With
Application.EnableEvents = True    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
Application.EnableEvents = False
With Sheet1
    Call UpdateMainProjectTable
End With
Application.EnableEvents = True    
End Sub

IN VBA unable to call subroutine in Sheet from Workbook event handlers Workbook_BeforeClose or Workbook_Deactivate()

2 Answers