BeforeClose Event

[sspatriots]

When I try the code below, I get a "Compile error: Wrong number of arguments or invalid property assignment" error. The ".close" part of the code is what is highlighted when I select "OK" to close the error message. I'm having a tough time seeing what would cause this. I just want the files below (all opened as "Read Only") to close when I exit my workbook. They are opened in the background because I have a lot of XLOOKUP formulas that point to them and I can only see those results when they are opened. Any help on this would be much appreciated. Thanks, SS

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim my_wb1 As Workbook
    Dim my_wb2 As Workbook
    Dim my_wb3 As Workbook
    Dim my_wb4 As Workbook
    
    Dim file_path1 As String
    Dim file_path2 As String
    Dim file_path3 As String
    Dim file_path4 As String
    
    file_path1 = "H:\Jobs\PO Block History.xlsm"
    file_path2 = "G:\Manufacturing\Manufacturing Detail Schedule1.xlsx"
    file_path3 = "H:\Shop Files\MRL Production Schedule\Production Schedule.xlsm"
    file_path4 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm"
    
    Set my_wb1 = Workbooks.Close(Filename:=file_path1, SaveChanges:=False)
    Set my_wb2 = Workbooks.Close(Filename:=file_path2, SaveChanges:=False)
    Set my_wb3 = Workbooks.Close(Filename:=file_path3, SaveChanges:=False)
    Set my_wb4 = Workbooks.Close(Filename:=file_path4, SaveChanges:=False)

End Sub

 

[DanteAmor]

It is not necessary to enter the path, only the file name.

Try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Workbooks("PO Block History.xlsm").Close SaveChanges:=False
  Workbooks("Manufacturing Detail Schedule1.xlsx").Close SaveChanges:=False
  Workbooks("Production Schedule.xlsm").Close SaveChanges:=False
  Workbooks("Job List.xlsm").Close SaveChanges:=False
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Joe4]

I don't know that it makes sense to try to set a Workbook variable to a workbook that you are closing.
What is the purpose of using Workbook objects? Why not just close them straight up, like Dante showed?

 

[rollis13]

Hi to all.
Also, On Error Resume Next on top may be needed to avoid error popup if the files aren't open.

 

[sspatriots]

It is not necessary to enter the path, only the file name.

Try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Workbooks("PO Block History.xlsm").Close SaveChanges:=False
  Workbooks("Manufacturing Detail Schedule1.xlsx").Close SaveChanges:=False
  Workbooks("Production Schedule.xlsm").Close SaveChanges:=False
  Workbooks("Job List.xlsm").Close SaveChanges:=False
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thank you. That did it....