Code to Arrange Remaining Windows after Closing One?

[Barbara]

I already use an Excel object event to automatically arrange Excel
windows when a new workbook is opened. What I want to do is
automatically arrange the remaining Excel windows after I *close* a
workbook. I have tried a number of events but I cannot get the results
I want.

TIA,

Barbara

 

[JE McGimpsey]

I'm assuming that you've tried the App_WorkbookBeforeClose event, and
that doesn't "get the results (you) want".

What results do you want?

The App_WorkbookDeactivate is the last event fired when a workbook
closes, but it also fires when you switch books, so if you could
differentiate between the two (set a flag in the WorkbookBeforeClose
event?), that would be a logical event to choose as well.

 

[Barbara]

I'm assuming that you've tried the App_WorkbookBeforeClose event, and
that doesn't "get the results (you) want".

When I use this event, there is *no* change to the windows arrangement,
because the code runs *before* the workbook window is closed. As such,
the arrangement doesn't changet. I want to automatically arrange the
remaining windows after the workbook is closed.

The App_WorkbookDeactivate is the last event fired when a workbook
closes, but it also fires when you switch books, so if you could
differentiate between the two (set a flag in the WorkbookBeforeClose
event?), that would be a logical event to choose as well.

The Deactivate event is the one that gave me trouble, exactly for the
reasons given. I'm a newbie at Visual Basic, so setting a flag is
something I haven't done. Why isn't there an App_WorkbookAfterClose
event?

 

[Tom Ogilvy]

In the beforeclose event, did you try minimizing the window of the closing
workbook, then issue your arrange command?

 

[JE McGimpsey]

When I use this event, there is *no* change to the windows arrangement,
because the code runs *before* the workbook window is closed. As such,
the arrangement doesn't changet. I want to automatically arrange the
remaining windows after the workbook is closed.

Since the App_WorkbookBeforeClose passes the closing workbook as an
argument, you could use

Private Sub MyWindow_WorkbookBeforeClose( _
ByVal Wb As Excel.Workbook, Cancel As Boolean)
Dim wkBk As Workbook
For Each wkBk In Application.Workbooks
If wkBk.Name <> Wb.Name Then
'arrange
End If
Next wkBk
End Sub

The Deactivate event is the one that gave me trouble, exactly for the
reasons given. I'm a newbie at Visual Basic, so setting a flag is
something I haven't done.

Something like this is a bit of a kludge, but it may work for you. The
flag bClosing is set by the App_WorkbookBeforeClose event macro. When
the workbook is deactivated, that flag is checked to see if it's the
result of a workbook closing or just switching workbooks. The
Application.OnTime code is added in the case that XL asks the user
whether to save and the user clicks "Cancel" - it clears the bClosing
flag after 15 seconds. It's not a perfect solution, as if the user
switches workbooks after canceling a save, App_WorkbookDeactivate will
treat it as a close event. You can change the timing to make that less
likely, but if the user then delays in saving, the arrangement might not
happen:

In the MyWindow (or whatever you name it) class module:

Private Sub MyWindow_WorkbookActivate(ByVal Wb As Excel.Workbook)
bClosing = False
End Sub

Private Sub MyWindow_WorkbookBeforeClose( _
ByVal Wb As Excel.Workbook, Cancel As Boolean)
bClosing = True
Application.OnTime Now + TimeSerial(0, 0, 15), "ClearFlag"
End Sub

Private Sub MyWindow_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
Dim wkBk As Workbook
If bClosing Then
For Each wkBk In Application.Workbooks
If wkBk.Name <> Wb.Name Then
'arrange
End If
Next wkBk
End If
End Sub

in a regular code module:

Public bClosing As Boolean

Public Sub ClearFlag()
bClosing = False
End Sub

Why isn't there an App_WorkbookAfterClose event?

Because MS hasn't exposed it?

 

[Barbara]

In the MyWindow (or whatever you name it) class module:

Private Sub MyWindow_WorkbookActivate(ByVal Wb As Excel.Workbook)
bClosing = False
End Sub

Private Sub MyWindow_WorkbookBeforeClose( _
ByVal Wb As Excel.Workbook, Cancel As Boolean)
bClosing = True
Application.OnTime Now + TimeSerial(0, 0, 15), "ClearFlag"
End Sub

Private Sub MyWindow_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
Dim wkBk As Workbook
If bClosing Then
For Each wkBk In Application.Workbooks
If wkBk.Name <> Wb.Name Then
'arrange
End If
Next wkBk
End If
End Sub

in a regular code module:

Public bClosing As Boolean

Public Sub ClearFlag()
bClosing = False
End Sub

Thanks for your help. That's quite a solution to a problem that I
originally thought would be very easy to solve.

I also created a keyboard shortcut that I can use to manually arrange
the open windows. I now think the manual method is preferable to using
an event. I'm surprised that automatically arranging windows after a
workbook is closed is so complex, when it's so simple to arrange them
when a workbook is opened. Go figure.

BTW- I'm using a Mac G5 with OS X, so the minimize trick won't work for
me.

Thanks again.

 

[JE McGimpsey]

Sure it will (I'm using a G4 with OSX):

ActiveWindow.WindowState = xlMinimized

MacXL doesn't have an Application.WindowState, since application in
MacOS aren't windowed as they are in Windows.