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
>
>
>
>
>
Similar Threads
