Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)... Cancel Argument ?

[Jaafar Tribak]

Hi,

Contrary to many other excel object model events, the Window Deactivate event does not provide a ByRef Cancel argument.

I was wondering if there is some workaround so that we could choose whether to allow the workbook window to be closed or to abort the closing action.

I am targeting the Window Deactivate event because it also fires when attempting to close a workbook window.

Obviously, we are talking here about a workbook that has more than one window. Otherwise, we could use the BeforeClose event.

I am open to your suggestions.
Regards.

 

[Jaafar Tribak]

Any thoughts or innovative idea on this ?

As a last resort, I am thinking of setting up a hook in the Deactivate event to trap the closing action, but I am not sure that would work and even if that does work, I was hoping to find a lighter workaround.

 

[Dan_W]

Hi Jaafar. I did give it some thought (and my thoughts/ideas could hardly be described as 'innovative'), but no luck I'm afraid. Sorry I couldn't be of any help.

 

[RoryA]

How would you determine that it was being closed, rather than just deactivated? Or does that not matter for your purposes?

 

[Jaafar Tribak]

@Dan_W
Thanks for taking a look.

@RoryA
No. I does matter. I want to prevent only the closing action but not the "deactivation" of the window.

I have in mind defining a Boolean Property (something like CancelClosing=TRUE) that can be set inside the window deactivate event only when the user decides to close the window... This would require code to be able to distinguish between closing and deactivating the window.

This in theory, could be achieved by setting a windows hook or by subclassing the window and watching for the closing event but, the idea of having a hook set througout the live of the window can be problematic (has a performance hit and poses a risk of crashing the application) hence the idea to set the hook only inside the Deactivate event, that way the hook would be set only temporarly & would last a brieve moment only at the time the window is being deactivated.

I havent had the time yet to start implementing this idea but I doubt it will work.

Regards.

 

[Dan_W]

Just a thought, but might there be something you can use from the Excel 4/5 macro language? I recall from my various travels around the 'world wide web' that Excel 4 (or 5?) had workbook/sheet events equivalent to those in VBA - OnSheetDeactivate. I don't remember exactly where I read that, and I don't know if there if it is anyway useful, but I can look through my browser history to see where I saw it if you think it might help. Personally, I would love it if the answer was found in an Excel 4 macro - that would just be a quintessentially Excel-y thing to happen.

 

[ZVI]

Hi Jaafar,

Not what you are asking for, the below code just recreates closed window of workbook:

' Code in ThisWorkbook to catch event one of the workbook's windows has been closed to recreate it

Option Explicit

Dim WnsCount As Long, WnIndex As Long
Dim SelAddress As String

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
 
  If Wn.Parent.Windows.Count < WnsCount Then
   
    MsgBox "Closed window will be recreated"
   
    ' Recreate previously closed window
    Application.EnableEvents = False
    Wn.NewWindow
    With Wn.Parent.Sheets(WnIndex)
      .Activate
      .Range(SelAddress).Select
      '...
    End With
   
    Application.EnableEvents = True
   
  End If
 
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
  WnsCount = Wn.Parent.Windows.Count
  WnIndex = ActiveSheet.Index
  SelAddress = Selection.Address
End Sub

 

[Jaafar Tribak]

Thank you guys for responding and I am sorry for not acknowledging your replies in good time... I have been having horrible sciatica pain and couldn't even sit on the computer chair