Create A New Event in Excel VBA

[abbey-abbey]

Is it possible at all to create entirely new events in Excel VBA, not just attaching to an existing one? What I've been trying to do and not succeeding so far is to create an event which lets me know before a window is closed: a "Window_BeforeClose()" event basically. Why do I want to do this? I have an Excel VBA project with two windows that have to stay open. One of my users managed to crash the project because he accidentally closed a window. So I want to know when someone tries to close a window and then override this action.

[si_the_geek]

Thread moved from the 'CodeBank' forum (which is for you to post your code examples, not questions) to the 'Office Development' forum

[Hack]

Are you using Excel UserForms?

[abbey-abbey]

No, I'm not using an Excel user form. The windows are standard workbook windows but opened as two windows so that instead of having "Book1" as the only opened window I have two windows named "Book1:1" and "Book1:2" or whatever the filename is by Excel.

I need both windows to be opened for use and they are addressed in code as windows(n).property where n = 1 or 2 in this case. If one of the windows gets close all windows(n).property codes result in errors being raised. I've worked round this for now by trapping these errors and re-opening the closed window. It would however be nicer and tidier to be able to stop the window being closed in the first place.

[Siddharth Rout]

AFAIK, you cannot create new events for an Excel object. The events are "hard coded" into the object, such as a Worksheet, Workbook, Window and may not be changed. You can, however, create custom events for your own classes.

[abbey-abbey]

Thanks koolsid. It explains why I've been banging my head against the wall.

[abbey-abbey]

AFAIK, you cannot create new events for an Excel object. The events are "hard coded" into the object, such as a Worksheet, Workbook, Window and may not be changed. You can, however, create custom events for your own classes.

How about this:

If I create a new class, can I represent the open window as a new object in this class and then create my own unique event to this new object?

Perhaps this way I can have a Window_BeforeClose() event?

[TheOnly]

Hi abbey,

a simple solution would be this small procedure in the Workbook_WindowActivate sub.

Advantage: From opening the workbook till closing - always 2 windows of the same file are open.

Disadvantage: File can only be closed by menu File->close
Flipping of the window if you want to close one
But a thing this one can be achieved.

best regards

TheOnly

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
 
  
   With ThisWorkbook
   
      If InStr(1, Wn.Caption, "xls:") = 0 Then
               .NewWindow
               .Windows.Arrange xlArrangeStyleHorizontal
      End If
   
   End With
End Sub

[Siddharth Rout]

@Abbey: I did go through your post 1 but could you explain more in detail about this "Window" and what exactly do you want to do?

[abbey-abbey]

I provided this detail to Hack earlier. My response is reproduced below:

"No, I'm not using an Excel user form. The windows are standard workbook windows but opened as two windows so that instead of having "Book1" as the only opened window I have two windows named "Book1:1" and "Book1:2", or whatever the filename is, by Excel.

I need both windows to be opened for use and they are addressed in code as windows(n).property where n = 1 or 2 in this case. If one of the windows gets close all windows(n).property codes result in errors being raised. I've worked round this for now by trapping these errors and re-opening the closed window. It would however be nicer and tidier to be able to stop the window being closed in the first place."

[abbey-abbey]

Thanks TheOnly. I already used a similar method to get round the problem. As you rightly point out, you get window flipping when the closed window is re-opened. This is also complicated because window(1) is always the active window regardless of whether or not its caption index is 1, 2 or whatever. So I had to sort this out in code and make sure the correct tabs are activated in the correct window and ...

That's why I'm so keen on a WindowBeforeClose event because it will make life so easy then.

[Siddharth Rout]

There is one way that I can think of ...

Creating this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
End Sub

during runtime in the workbooks that you open. because when you close the Window, the Workbook_BeforeClose event is fired... probably that might help you with your query..

[abbey-abbey]

Unfortunately this will not work because Workbook_BeforeClose event only gets fired when you try to close the workbook or the last window in a multi-window workbook but not when you close a 1 of n window.

[TheOnly]

Hi abbey,

Have found the solution hat window 1 is always on top regardless which of the 2 windows do you close.

The class thing would be wonderfull but I think this is only managable with API stuff and thats not so easy.

best regards

theOnly

 With ThisWorkbook
      'look if we found a more than one windows workbook
      If InStr(1, Wn.Caption, "xls:") = 0 Then
               ' no - create a new window
               .NewWindow

               'imediate change to the Window number 1
               'so that win 1 will be always on top of the screen
               .Windows(ThisWorkbook.Name & ":1").Activate

               'now we can arrange the workbook
               .Windows.Arrange xlArrangeStyleHorizontal
      End If
   
   End With