Need code to close minimized workbooks in BeforeClose event

[Cheryl]

I've written code to open a workbook and set path and file name variables
based on values in named ranges of the open workbook. I then use these
variables to open three additional workbooks and minimize them. All that is
working fine. However, I need to close the three minimized workbooks when I
close the main workbook, I presume in the BeforeClose event. I can't seem to
figure out how to do this. I tried setting public variables for the 3
workbooks, but that's not working either. I'm a novice, obviously. Can anyone
supply the code to accomplish this task?

Here's the code I've written to open the 3 supporting workbooks:

Public Wbk1 As Workbook
Public Wbk2 As Workbook
Public Wbk3 As Workbook


Private Sub Workbook_Open()

Application.ScreenUpdating = False

Dim Pname1 As String, Pname2 As String, Pname3 As String
Dim Fname1 As String, Fname2 As String, Fname3 As String
Dim Bk1 As String, Bk2 As String, Bk3 As String

Worksheets("Display").Activate

Pname1 = Worksheets("Display").Range("Path1")
Fname1 = Worksheets("Display").Range("File1")
Pname2 = Worksheets("Display").Range("Path2")
Fname2 = Worksheets("Display").Range("File2")
Pname3 = Worksheets("Display").Range("Path3")
Fname3 = Worksheets("Display").Range("File3")

Bk1 = Pname1 + "\" + Fname1
Bk2 = Pname2 + "\" + Fname2
Bk3 = Pname3 + "\" + Fname3

Workbooks.Open (Bk1)
Set Wbk1 = ActiveWorkbook
ActiveWindow.WindowState = xlMinimized
Workbooks.Open (Bk2)
Set Wbk2 = ActiveWorkbook
ActiveWindow.WindowState = xlMinimized
Workbooks.Open (Bk3)
Set Wbk3 = ActiveWorkbook
ActiveWindow.WindowState = xlMinimized

ActiveWindow.WindowState = xlMaximized
Worksheets("Display").Range("A24").Activate

End Sub

Thanks for any help!

Cheryl

 

[Zone]

Try this:

Dim w As Window
For Each w In Windows
If w.WindowState = xlMinimized Then w.Close
Next

or, to save before closing

Dim w As Window
For Each w In Windows
If w.WindowState = xlMinimized Then w.Close savechanges:=true
Next

HTH, James

 

[Dave Peterson]

My choice is to not do this kind of stuff in the _beforeclose event.

If you decide to do this, then you'll have to add a save command to your code.
And if I (or the user) opened the workbook made changes and decide to close
without saving, you're save could destroy the workbook.

If you really want the workbooks minimized when they're open, put the code in
each workbook's Workbook_Open event.

I find it much easier and much, much safer.

 

[Cheryl]

Actually, I'm doing it in the beforeclose event in the main workbook. I only
open the other workbooks because I use an INDIRECT function in it to
concatenate path and file names for the external references, and those
external references are based on the value of a named range, which can
change. And when I close the main workbook, I also want to close only those 3
specific external workbooks, without saving, and leave any other workbooks
the user might have open alone.

I don't want to put the minimize in the external workbook's on open event,
because they get used for other purposes, and I don't want them to minimize
every time the user opens them.

I finally figured it out, and it's working just the way I want it to. Thanks
for the feedback, I appreciate it!

Cheryl