Macro to autoclose hidden workbooks is closing all active workbooks

[tlchavez]

Good afternoon. I have a macro set to auto-open several linked workbooks for a data reference workbook and these sheets are hidden. When the user closes the data reference workbook there is a macro that runs to auto close the hidden workbooks. This works great, unless the user has other workbooks open at that same time. If the user has other workbooks open then it will close all the active workbooks and not ask for any changes to be saved. The next time the user goes to open the other active workbooks it gives them the auto recover option. Is there any way to keep the macro from closing the workbooks that are not listed in the macro (linked to the data reference workbook)?

Sincerely,

Tammy

[AndyLitch]

The auto_Open obviously opens specific workbooks whereas the auto_close shuts down everything... use the list from the open macro to explicitly close the related workbooks only..

[tlchavez]

Hi Andy. Thank you for that reply but we do have the specific files listed in the Auto Close. I am copying it here so you can see what we have.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Workbooks("GDWL UNITS PRODUCED CURRENT YEAR.xlsx").Close SaveChanges:=False
Workbooks("GDWL UNITS SOLD CURRENT YEAR.xlsx").Close SaveChanges:=False
Workbooks("GDWL Sales Current Year.xlsx").Close SaveChanges:=False
Workbooks("GDWL UNITS PRODUCED HISTORY YEAR.xlsx").Close SaveChanges:=False
Workbooks("GDWL UNITS SOLD HISTORY YEAR.xlsx").Close SaveChanges:=False
Workbooks("GDWL Sales History Year.xlsx").Close SaveChanges:=False
Workbooks("GDWL DONORS_TRANSACTIONS_CURRENT_YEAR.xlsx").Close SaveChanges:=False
Workbooks("GDWL DONORS_TRANSACTIONS_HISTORY_YEARS.xlsx").Close SaveChanges:=False
Workbooks("GDWL Budget Numbers Current and History Years.xlsx").Close SaveChanges:=False
Workbooks("GDWL Donations Current Year.xlsx").Close SaveChanges:=False
Workbooks("GDWL UNITS PULLED CURRENT YEAR.xlsx").Close SaveChanges:=False
On Error GoTo 0
Me.Close SaveChanges:=False

End Sub


The worksheet that these are linked to is a read only file as we don't want the users to make any changes to the worksheet.

Thank you again,
Tammy

[AndyLitch]

In that case Tammy, I'm a bit stumped as to why it's closing down the additional workbooks. Unless maybe they have the same name as one targeted for closure ???

[tlchavez]

They shouldn't have the same name as any of the ones listed or the one they are linked to. I have encountered this problem on a workstation and their server.

Thanks for trying to help. :-)

Tammy