Hi
I have an addin with a class module which sometimes gives problems after
altering code. I find the class events do not fire until I have stepped
through a procedure in the module. Whenever I change the class code I always
compile and save. But once the events begin to fire there are no further
problems in any scenario.
Can anyone throw some light o what is going wrong and how it may be put right?

T.I.A.

Geoff

In ThisWorkBook module:

Dim oAppEvents As CAppEvents
Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub

In CAppEvents class module:

Dim WithEvents oApp As Application
Private Sub Class_Initialize()
Set oApp = Application
End Sub
then various events:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookAddinUninstall(ByVal Wb As Workbook)
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Private Sub oApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Workbook)
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Often when you edit code, you reset any objects that may have been created.
You need to rerun the line of code in the Workbook_Open procedure of the
ThisWorkbook module. Maybe put it into a FixMe() sub that you can run to
reset anything else that gets messed up, like the states of ScreenUpdating,
EnableEvents, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Geoff" wrote in message
...
Hi
I have an addin with a class module which sometimes gives problems after
altering code. I find the class events do not fire until I have stepped
through a procedure in the module. Whenever I change the class code I
always
compile and save. But once the events begin to fire there are no further
problems in any scenario.
Can anyone throw some light o what is going wrong and how it may be put
right?

T.I.A.

Geoff

In ThisWorkBook module:

Dim oAppEvents As CAppEvents
Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub

In CAppEvents class module:

Dim WithEvents oApp As Application
Private Sub Class_Initialize()
Set oApp = Application
End Sub
then various events:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookAddinUninstall(ByVal Wb As Workbook)
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Private Sub oApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As
Boolean, Cancel As Boolean)
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Workbook)
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

If I understand correctly you are suggesting that during development I set
all objects to nothing etc after code changes. I can understand the
reasoning but doesn't the same thing happen if the app is closed and
reopened? There have been times when I have shut the app and reopened but
the problem still persists. Silly as it may seem I have put a MsgBox ""
statement in both the WorkBook_Open event and also the oApp_WorkbookOpen
event in order to step through the code. This works but is obviously not a
practical approach.

Geoff

"Jon Peltier" wrote:

Often when you edit code, you reset any objects that may have been created.
You need to rerun the line of code in the Workbook_Open procedure of the
ThisWorkbook module. Maybe put it into a FixMe() sub that you can run to
reset anything else that gets messed up, like the states of ScreenUpdating,
EnableEvents, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Geoff" wrote in message
...
Hi
I have an addin with a class module which sometimes gives problems after
altering code. I find the class events do not fire until I have stepped
through a procedure in the module. Whenever I change the class code I
always
compile and save. But once the events begin to fire there are no further
problems in any scenario.
Can anyone throw some light o what is going wrong and how it may be put
right?

T.I.A.

Geoff

In ThisWorkBook module:

Dim oAppEvents As CAppEvents
Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub

In CAppEvents class module:

Dim WithEvents oApp As Application
Private Sub Class_Initialize()
Set oApp = Application
End Sub
then various events:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookAddinUninstall(ByVal Wb As Workbook)
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Private Sub oApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As
Boolean, Cancel As Boolean)
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Workbook)
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.EnableEvents = False

is a persistent setting. Restarting XL does not reset it. Once you turn it
off the only way it is coming back on is if you reset it via code...
--
HTH...

Jim Thomlinson


"Geoff" wrote:

If I understand correctly you are suggesting that during development I set
all objects to nothing etc after code changes. I can understand the
reasoning but doesn't the same thing happen if the app is closed and
reopened? There have been times when I have shut the app and reopened but
the problem still persists. Silly as it may seem I have put a MsgBox ""
statement in both the WorkBook_Open event and also the oApp_WorkbookOpen
event in order to step through the code. This works but is obviously not a
practical approach.

Geoff

"Jon Peltier" wrote:

Often when you edit code, you reset any objects that may have been created.
You need to rerun the line of code in the Workbook_Open procedure of the
ThisWorkbook module. Maybe put it into a FixMe() sub that you can run to
reset anything else that gets messed up, like the states of ScreenUpdating,
EnableEvents, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Geoff" wrote in message
...
Hi
I have an addin with a class module which sometimes gives problems after
altering code. I find the class events do not fire until I have stepped
through a procedure in the module. Whenever I change the class code I
always
compile and save. But once the events begin to fire there are no further
problems in any scenario.
Can anyone throw some light o what is going wrong and how it may be put
right?

T.I.A.

Geoff

In ThisWorkBook module:

Dim oAppEvents As CAppEvents
Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub

In CAppEvents class module:

Dim WithEvents oApp As Application
Private Sub Class_Initialize()
Set oApp = Application
End Sub
then various events:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookAddinUninstall(ByVal Wb As Workbook)
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Private Sub oApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As
Boolean, Cancel As Boolean)
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Workbook)
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

That is interesting.
Though I have not used EnableEvents = False I wonder now if there may be
other settings which may persist.

It is odd that once any event in the class module has fired then the xla
performs as expected. I have been very careful in the shutdown of the xla
but was unaware of 'persistence'. But what??

Geoff


"Jim Thomlinson" wrote:

Application.EnableEvents = False

is a persistent setting. Restarting XL does not reset it. Once you turn it
off the only way it is coming back on is if you reset it via code...
--
HTH...

Jim Thomlinson


"Geoff" wrote:

If I understand correctly you are suggesting that during development I set
all objects to nothing etc after code changes. I can understand the
reasoning but doesn't the same thing happen if the app is closed and
reopened? There have been times when I have shut the app and reopened but
the problem still persists. Silly as it may seem I have put a MsgBox ""
statement in both the WorkBook_Open event and also the oApp_WorkbookOpen
event in order to step through the code. This works but is obviously not a
practical approach.

Geoff

"Jon Peltier" wrote:

Often when you edit code, you reset any objects that may have been created.
You need to rerun the line of code in the Workbook_Open procedure of the
ThisWorkbook module. Maybe put it into a FixMe() sub that you can run to
reset anything else that gets messed up, like the states of ScreenUpdating,
EnableEvents, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Geoff" wrote in message
...
Hi
I have an addin with a class module which sometimes gives problems after
altering code. I find the class events do not fire until I have stepped
through a procedure in the module. Whenever I change the class code I
always
compile and save. But once the events begin to fire there are no further
problems in any scenario.
Can anyone throw some light o what is going wrong and how it may be put
right?

T.I.A.

Geoff

In ThisWorkBook module:

Dim oAppEvents As CAppEvents
Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub

In CAppEvents class module:

Dim WithEvents oApp As Application
Private Sub Class_Initialize()
Set oApp = Application
End Sub
then various events:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookAddinUninstall(ByVal Wb As Workbook)
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Private Sub oApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
Private Sub oApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As
Boolean, Cancel As Boolean)
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Private Sub oApp_WorkbookDeactivate(ByVal Wb As Workbook)
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)