Hi All

I've got the following code in the "ThisWorkbook" code object.

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

Cancel = False

If Left(Wb.Name, 4) < "Book" Then
MsgBox "beep"
Cancel = True
Exit Sub
End If

End Sub



Public Sub SetApp()

Set App = Application

End Sub


and in another module

ThisWorkbook.SetApp

the problem i'm having is that the "App" object clears as soon as
focus returns to the calling module. Anyone have any idea why, i'm at
a complete loss on this one.

cheers

Keith

Your a little off in how you have set this up. The gist of what you want is
to instantiate an object of your class. You are not using a class module. you
put the code in this workbook whcih will not work. Add a class module to your
project. That object will contain an instance of XL which listens for XL
events. So you are close but you need to make a couple of tweaks...

Add a class Module and name it something like clsXLEvents
Put your code into that class.

When you instantiate an object of your class you want to create the XL
instance so use the Class Initialization event something like this...

Private Sub Class_Initialize()
Set App = Application
End Sub

Instead of
Public Sub SetApp()
Set App = Application
End Sub


Now you just need to create an instance of your class something like this...
In a standard module publicly declare an object of type clsXLEvents

public AppEvents as clsXLEvents
Somewhere in code (ususally in the open event of this workbook) add
set AppEvents = new clsXLEvents

Check out this link for more info on application level events...
http://www.cpearson.com/excel/AppEvent.aspx

--
HTH...

Jim Thomlinson


"Keith74" wrote:

Hi All

I've got the following code in the "ThisWorkbook" code object.

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

Cancel = False

If Left(Wb.Name, 4) < "Book" Then
MsgBox "beep"
Cancel = True
Exit Sub
End If

End Sub



Public Sub SetApp()

Set App = Application

End Sub


and in another module

ThisWorkbook.SetApp

the problem i'm having is that the "App" object clears as soon as
focus returns to the calling module. Anyone have any idea why, i'm at
a complete loss on this one.

cheers

Keith

Thanks Jim, i'll give that a go

Yn newyddion: ,
Roedd Jim Thomlinson wedi
ysgrifennu:

You are not using a
class module. you put the code in this workbook whcih will not work.

ThisWorkbook /is/ as class module. I just tried the OP's code and it worked
just fine. After running Thisworkbook.setapp, "?thisworkbook.App is nothing"
in the immediate window returns false, indicating that the App variable
still refers to the Application object.

Rob