Hi



I am using the software from (the peerless) Chip Pearson's site that helps
with Application Event programming.



The event I have is as follows:

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)

Etc etc

End Sub



Some time ago someone on the ng helped me by supplying this code to deal
with the situation when the VB Project has <fallen over> e.g. if a Run-Reset
has happened etc or a runtime error has been finished by the user pressing
"End" on the dialog box.

Sub Reset_EnableEvents()

Set AppClass.App = Application

End Sub



Is there a property in VB that can identify if that reset has occurred i.e.
can one determine programmatically if it is necessary to run the procedure
Reset_EnableEvents?



Thanks



Tim

Make a public variable, in a normal module and call it for example:
bWBOpened,
so: Public bWBOpened as Boolean

In the Workbook Open event, so in:

Private Sub Workbook_Open()

put this:

bWBOpened = True

Now test for this variable, so:

If bWBOpened = False Then
Reset_EnableEvents
End If


RBS



"Tim Childs" <(E-Mail Removed)> wrote in message
news:eMO$(E-Mail Removed)...
> Hi
>
>
>
> I am using the software from (the peerless) Chip Pearson's site that helps
> with Application Event programming.
>
>
>
> The event I have is as follows:
>
> Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
>
> Etc etc
>
> End Sub
>
>
>
> Some time ago someone on the ng helped me by supplying this code to deal
> with the situation when the VB Project has <fallen over> e.g. if a
> Run-Reset
> has happened etc or a runtime error has been finished by the user pressing
> "End" on the dialog box.
>
> Sub Reset_EnableEvents()
>
> Set AppClass.App = Application
>
> End Sub
>
>
>
> Is there a property in VB that can identify if that reset has occurred
> i.e.
> can one determine programmatically if it is necessary to run the procedure
> Reset_EnableEvents?
>
>
>
> Thanks
>
>
>
> Tim
>
>
>
>
>
>
>
>

Hi Tim,

If I follow you are trapping application level events and want to cater for
the possibility the class holding the app ref has been destroyed, eg by user
pressing the reset button. You could set a boolean flag, which if false
would indicate your variables have been destroyed. However you might just as
well amend your routine -

Sub Reset_EnableEvents()
If AppClass is nothing then
set AppClass = new ClassName
Set AppClass.App = Application
end if

End Sub

I can't think of anything that will automatically tell you that your
AppClass no longer exists, so you would need to check periodically. In
theory you could trap the VBE Reset button-click event but there are other
ways your ref could be destroyed. Perhaps look into the OnTime method with
Schedule to run the amended Reset_EnableEvents routine (don't forget to
clear it when done).

Regards,
Peter T



"Tim Childs" <(E-Mail Removed)> wrote in message
news:eMO$(E-Mail Removed)...
> Hi
>
>
>
> I am using the software from (the peerless) Chip Pearson's site that helps
> with Application Event programming.
>
>
>
> The event I have is as follows:
>
> Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
>
> Etc etc
>
> End Sub
>
>
>
> Some time ago someone on the ng helped me by supplying this code to deal
> with the situation when the VB Project has <fallen over> e.g. if a
Run-Reset
> has happened etc or a runtime error has been finished by the user pressing
> "End" on the dialog box.
>
> Sub Reset_EnableEvents()
>
> Set AppClass.App = Application
>
> End Sub
>
>
>
> Is there a property in VB that can identify if that reset has occurred
i.e.
> can one determine programmatically if it is necessary to run the procedure
> Reset_EnableEvents?
>
>
>
> Thanks
>
>
>
> Tim
>
>
>
>
>
>
>
>

Hi

thanks, I will try that

Tim


"RB Smissaert" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Make a public variable, in a normal module and call it for example:
> bWBOpened,
> so: Public bWBOpened as Boolean
>
> In the Workbook Open event, so in:
>
> Private Sub Workbook_Open()
>
> put this:
>
> bWBOpened = True
>
> Now test for this variable, so:
>
> If bWBOpened = False Then
> Reset_EnableEvents
> End If
>
>
> RBS
>
>
>
> "Tim Childs" <(E-Mail Removed)> wrote in message
> news:eMO$(E-Mail Removed)...
> > Hi
> >
> >
> >
> > I am using the software from (the peerless) Chip Pearson's site that
helps
> > with Application Event programming.
> >
> >
> >
> > The event I have is as follows:
> >
> > Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
> >
> > Etc etc
> >
> > End Sub
> >
> >
> >
> > Some time ago someone on the ng helped me by supplying this code to deal
> > with the situation when the VB Project has <fallen over> e.g. if a
> > Run-Reset
> > has happened etc or a runtime error has been finished by the user
pressing
> > "End" on the dialog box.
> >
> > Sub Reset_EnableEvents()
> >
> > Set AppClass.App = Application
> >
> > End Sub
> >
> >
> >
> > Is there a property in VB that can identify if that reset has occurred
> > i.e.
> > can one determine programmatically if it is necessary to run the
procedure
> > Reset_EnableEvents?
> >
> >
> >
> > Thanks
> >
> >
> >
> > Tim
> >
> >
> >
> >
> >
> >
> >
> >
>

Hi Peter

Thanks for response. Can someone explain what the suggested change to the
procedure

> Sub Reset_EnableEvents()
> If AppClass is nothing then
> set AppClass = new ClassName
> Set AppClass.App = Application
> end if
>
> End Sub

is doing because I dont yet understand.

Thanks

Tim

"Peter T" <peter_t@discussions> wrote in message
news:#(E-Mail Removed)...
> Hi Tim,
>
> If I follow you are trapping application level events and want to cater
for
> the possibility the class holding the app ref has been destroyed, eg by
user
> pressing the reset button. You could set a boolean flag, which if false
> would indicate your variables have been destroyed. However you might just
as
> well amend your routine -
>
> Sub Reset_EnableEvents()
> If AppClass is nothing then
> set AppClass = new ClassName
> Set AppClass.App = Application
> end if
>
> End Sub
>
> I can't think of anything that will automatically tell you that your
> AppClass no longer exists, so you would need to check periodically. In
> theory you could trap the VBE Reset button-click event but there are other
> ways your ref could be destroyed. Perhaps look into the OnTime method with
> Schedule to run the amended Reset_EnableEvents routine (don't forget to
> clear it when done).
>
> Regards,
> Peter T
>
>

If AppClass is Nothing then ...

If your previously created class, that holds the ref to Excel (AppClass.App
= Application) and handles application 'withevents', has unexpectedly become
nothing, ie it's been destroyed, you can go on to recreate or 'reset' it.

You could of course use RBS's suggestion to check 'If Not bWBOpened then...'
anywhere in your code and go on to reset your objects, and perhaps any other
global variables.

If you are going to check your settings periodically with the OnTime method
you will need to call some routine, which might just as well be your
existing Sub Reset_EnableEvents(). But in the routine only reset if
necessary.

Regards,
Peter T


"Tim Childs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Peter
>
> Thanks for response. Can someone explain what the suggested change to the
> procedure
>
> > Sub Reset_EnableEvents()
> > If AppClass is nothing then
> > set AppClass = new ClassName
> > Set AppClass.App = Application
> > end if
> >
> > End Sub
>
> is doing because I dont yet understand.
>
> Thanks
>
> Tim
>
> "Peter T" <peter_t@discussions> wrote in message
> news:#(E-Mail Removed)...
> > Hi Tim,
> >
> > If I follow you are trapping application level events and want to cater
> for
> > the possibility the class holding the app ref has been destroyed, eg by
> user
> > pressing the reset button. You could set a boolean flag, which if false
> > would indicate your variables have been destroyed. However you might
just
> as
> > well amend your routine -
> >
> > Sub Reset_EnableEvents()
> > If AppClass is nothing then
> > set AppClass = new ClassName
> > Set AppClass.App = Application
> > end if
> >
> > End Sub
> >
> > I can't think of anything that will automatically tell you that your
> > AppClass no longer exists, so you would need to check periodically. In
> > theory you could trap the VBE Reset button-click event but there are
other
> > ways your ref could be destroyed. Perhaps look into the OnTime method
with
> > Schedule to run the amended Reset_EnableEvents routine (don't forget to
> > clear it when done).
> >
> > Regards,
> > Peter T
> >
> >
>
>

Hi Peter

Many thanks for response

I will try to assimilate in the programming. The underlying problem is that
sometimes the program simply does not start up properly when the file
containing it is opened. The problem is intermittent so I was very
interested in being able to test as directly as possible, rather than by
proxy e.g. the proxy would be testing the value of a new boolean flag
variable, although I will incorporate that as well.

Best wishes
Tim


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> If AppClass is Nothing then ...
>
> If your previously created class, that holds the ref to Excel
(AppClass.App
> = Application) and handles application 'withevents', has unexpectedly
become
> nothing, ie it's been destroyed, you can go on to recreate or 'reset' it.
>
> You could of course use RBS's suggestion to check 'If Not bWBOpened
then...'
> anywhere in your code and go on to reset your objects, and perhaps any
other
> global variables.
>
> If you are going to check your settings periodically with the OnTime
method
> you will need to call some routine, which might just as well be your
> existing Sub Reset_EnableEvents(). But in the routine only reset if
> necessary.
>
> Regards,
> Peter T
>
>
> "Tim Childs" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Peter
> >
> > Thanks for response. Can someone explain what the suggested change to
the
> > procedure
> >
> > > Sub Reset_EnableEvents()
> > > If AppClass is nothing then
> > > set AppClass = new ClassName
> > > Set AppClass.App = Application
> > > end if
> > >
> > > End Sub
> >
> > is doing because I dont yet understand.
> >
> > Thanks
> >
> > Tim
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:#(E-Mail Removed)...
> > > Hi Tim,
> > >
> > > If I follow you are trapping application level events and want to
cater
> > for
> > > the possibility the class holding the app ref has been destroyed, eg
by
> > user
> > > pressing the reset button. You could set a boolean flag, which if
false
> > > would indicate your variables have been destroyed. However you might
> just
> > as
> > > well amend your routine -
> > >
> > > Sub Reset_EnableEvents()
> > > If AppClass is nothing then
> > > set AppClass = new ClassName
> > > Set AppClass.App = Application
> > > end if
> > >
> > > End Sub
> > >
> > > I can't think of anything that will automatically tell you that your
> > > AppClass no longer exists, so you would need to check periodically. In
> > > theory you could trap the VBE Reset button-click event but there are
> other
> > > ways your ref could be destroyed. Perhaps look into the OnTime method
> with
> > > Schedule to run the amended Reset_EnableEvents routine (don't forget
to
> > > clear it when done).
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> >
> >
>
>

Hi

I could not get the code below to compile:
Sub Reset_EnableEvents_NEW()
If AppClass Is Nothing Then
Set AppClass = New ClassName
Set AppClass.App = Application
End If
End Sub

"New ClassName" above produced the compile error:
"User-defined type not defined"

I had Option Explicit turned on in the module

Any help welcome. THANKS

Tim


"Tim Childs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Peter
>
> Many thanks for response
>
> I will try to assimilate in the programming. The underlying problem is
that
> sometimes the program simply does not start up properly when the file
> containing it is opened. The problem is intermittent so I was very
> interested in being able to test as directly as possible, rather than by
> proxy e.g. the proxy would be testing the value of a new boolean flag
> variable, although I will incorporate that as well.
>
> Best wishes
> Tim
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
> > If AppClass is Nothing then ...
> >
> > If your previously created class, that holds the ref to Excel
> (AppClass.App
> > = Application) and handles application 'withevents', has unexpectedly
> become
> > nothing, ie it's been destroyed, you can go on to recreate or 'reset'
it.
> >
> > You could of course use RBS's suggestion to check 'If Not bWBOpened
> then...'
> > anywhere in your code and go on to reset your objects, and perhaps any
> other
> > global variables.
> >
> > If you are going to check your settings periodically with the OnTime
> method
> > you will need to call some routine, which might just as well be your
> > existing Sub Reset_EnableEvents(). But in the routine only reset if
> > necessary.
> >
> > Regards,
> > Peter T
> >
> >
> > "Tim Childs" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hi Peter
> > >
> > > Thanks for response. Can someone explain what the suggested change to
> the
> > > procedure
> > >
> > > > Sub Reset_EnableEvents()
> > > > If AppClass is nothing then
> > > > set AppClass = new ClassName
> > > > Set AppClass.App = Application
> > > > end if
> > > >
> > > > End Sub
> > >
> > > is doing because I dont yet understand.
> > >
> > > Thanks
> > >
> > > Tim
>

Hi Tim,

I understood from the onset that you are trapping application level events,
so you would have a class module named [say] ClassName, in the class module
something like this

Public withevents App as Excel.application

also, at module level in a normal module you would maintain a reference to
your class named [say] ClassName

Dim AppClass as ClassName

In my sample code. not knowing what you named your class I wrote at random
'ClassName'. You will need to change this to whatever you named your class,
perhaps it's still named 'Class1'.

Regards,
Peter T


"Tim Childs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I could not get the code below to compile:
> Sub Reset_EnableEvents_NEW()
> If AppClass Is Nothing Then
> Set AppClass = New ClassName
> Set AppClass.App = Application
> End If
> End Sub
>
> "New ClassName" above produced the compile error:
> "User-defined type not defined"
>
> I had Option Explicit turned on in the module
>
> Any help welcome. THANKS
>
> Tim
>
>
> "Tim Childs" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Peter
> >
> > Many thanks for response
> >
> > I will try to assimilate in the programming. The underlying problem is
> that
> > sometimes the program simply does not start up properly when the file
> > containing it is opened. The problem is intermittent so I was very
> > interested in being able to test as directly as possible, rather than by
> > proxy e.g. the proxy would be testing the value of a new boolean flag
> > variable, although I will incorporate that as well.
> >
> > Best wishes
> > Tim
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:(E-Mail Removed)...
> > > If AppClass is Nothing then ...
> > >
> > > If your previously created class, that holds the ref to Excel
> > (AppClass.App
> > > = Application) and handles application 'withevents', has unexpectedly
> > become
> > > nothing, ie it's been destroyed, you can go on to recreate or 'reset'
> it.
> > >
> > > You could of course use RBS's suggestion to check 'If Not bWBOpened
> > then...'
> > > anywhere in your code and go on to reset your objects, and perhaps any
> > other
> > > global variables.
> > >
> > > If you are going to check your settings periodically with the OnTime
> > method
> > > you will need to call some routine, which might just as well be your
> > > existing Sub Reset_EnableEvents(). But in the routine only reset if
> > > necessary.
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > > "Tim Childs" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Hi Peter
> > > >
> > > > Thanks for response. Can someone explain what the suggested change
to
> > the
> > > > procedure
> > > >
> > > > > Sub Reset_EnableEvents()
> > > > > If AppClass is nothing then
> > > > > set AppClass = new ClassName
> > > > > Set AppClass.App = Application
> > > > > end if
> > > > >
> > > > > End Sub
> > > >
> > > > is doing because I dont yet understand.
> > > >
> > > > Thanks
> > > >
> > > > Tim
> >
>
>

Hi Peter

I seem to get it to work when I change the code as follows:

Sub Reset_EnableEvents_NEW()
'If AppClass Is Nothing Then
' 'Set AppClass = New ClassName
' Set AppClass = New EventClass
' Set AppClass.App = Application
'End If
If AppClass.App Is Nothing Then
'MsgBox "found it"
Set AppClass.App = Application
End If
End Sub

When I just used:
If AppClass Is Nothing Then
...
End If
I did not trap the problem, I seemed to have to use:
If AppClass.App Is Nothing
to trap the <falling over>

Any explanation of this would be welcome.

The help is much appreciated

Tim

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Hi Tim,
>
> I understood from the onset that you are trapping application level
events,
> so you would have a class module named [say] ClassName, in the class
module
> something like this
>
> Public withevents App as Excel.application
>
> also, at module level in a normal module you would maintain a reference to
> your class named [say] ClassName
>
> Dim AppClass as ClassName
>
> In my sample code. not knowing what you named your class I wrote at random
> 'ClassName'. You will need to change this to whatever you named your
class,
> perhaps it's still named 'Class1'.
>
> Regards,
> Peter T
>
>
> "Tim Childs" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi
> >
> > I could not get the code below to compile:
> > Sub Reset_EnableEvents_NEW()
> > If AppClass Is Nothing Then
> > Set AppClass = New ClassName
> > Set AppClass.App = Application
> > End If
> > End Sub
> >
> > "New ClassName" above produced the compile error:
> > "User-defined type not defined"
> >
> > I had Option Explicit turned on in the module
> >
> > Any help welcome. THANKS
> >
> > Tim
> >
> >
> > "Tim Childs" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hi Peter
> > >
> > > Many thanks for response
> > >
> > > I will try to assimilate in the programming. The underlying problem is
> > that
> > > sometimes the program simply does not start up properly when the file
> > > containing it is opened. The problem is intermittent so I was very
> > > interested in being able to test as directly as possible, rather than
by
> > > proxy e.g. the proxy would be testing the value of a new boolean flag
> > > variable, although I will incorporate that as well.
> > >
> > > Best wishes
> > > Tim
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:(E-Mail Removed)...
> > > > If AppClass is Nothing then ...
> > > >
> > > > If your previously created class, that holds the ref to Excel
> > > (AppClass.App
> > > > = Application) and handles application 'withevents', has
unexpectedly
> > > become
> > > > nothing, ie it's been destroyed, you can go on to recreate or
'reset'
> > it.
> > > >
> > > > You could of course use RBS's suggestion to check 'If Not bWBOpened
> > > then...'
> > > > anywhere in your code and go on to reset your objects, and perhaps
any
> > > other
> > > > global variables.
> > > >
> > > > If you are going to check your settings periodically with the OnTime
> > > method
> > > > you will need to call some routine, which might just as well be your
> > > > existing Sub Reset_EnableEvents(). But in the routine only reset if
> > > > necessary.
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > >