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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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