For theory look into Class Modules


The "normal" way of doing this is creating a "Class Module"
however for just monitoring excel's application it's just as easy to do
it in a workbook's object module.

simple example of the latter

'Thisworkbook object module
Option Explicit

Dim WithEvents xlsMonitor As Excel.Application

Private Sub Workbook_Open()
'instantiate the appMonitor
Set xlsMonitor = Application
End Sub

Private Sub xlsMonitor_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

'fires on changes in ALL sheets in ALL workbooks
Debug.Print "Change in:"; Target.Address(external:=True)
End Sub



NB.With Thisworkbook's codemodulewindow..
TopLeft dropdown = select the xlsMonitor object
TopRight drowdown = Select all the exposed event handlers.


1. For testing ensure the object is instantiated by running
workbook_open.

2. Then type in some open workbooks.. and watch the immediate screen.








keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Wexler) wrote:

Yeah, thisapp and thiswb.application point to the same thing.
I tried the doEvents, but no luck. Unfortunately I can't send you the
code.
I don't know much about the WITHEVENTS APPLICATION object, but it
sounds like that is really what I should be using. What are some good
resources to find out more about this?
Thanks again for your help. This one really has me stumped....


keepITcool wrote in message
. ..
I can follow your explanation but can't pinpoint
the cause.

What surprises me is you dont seem to be using
a WITHEVENTS APPLICATION object in either of your "containers"?

That way you can clear all the event code for the "XLS", since you
can simply monitor all events within the running instance from 1
location.

What I do see is:
since I cant tell the scope of certain veriable you use..
e.g. are you sure the ThisApp application object variable
points to the same application instance as ThisWb.Application?

.. apparently you've checked this...

as a last resort...
include a DoEvents to give all the callbacks time
to be processed.

Else

If possible mail me the XLS, XLA and the DLL's VBA project
I'm willing to help you debug or suggest "informed" alternatives.
but like this it's just guessing.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Wexler) wrote:

This is the setup:
I have a regular excel workbook, and all its event handlers just
call a sub in an excel add-in. The excel add-in then makes a call
to an ActiveX DLL. The DLL has a global variable reference to the
original workbook. So, the user clicks on a cell, which fires the
worksheetselection_change event in the workbook, which calls a sub
in the add-in, which calls a sub in the DLL. This sub fires a form
which resides in the DLL. The user can type a value in a text box,
click OK, and the code in the OK_click event of the form in the DLL
writes this value directly to the original workbook.
I have been using this same setup for everything, I have 50 or so
forms which all work, and all generate events in the original
workbook in situations like this one. But for some reason in this
particular instance, it doesn't. The DLL has a handle on the right
workbook, and right sheet, because the value is written to the
right place. Here is the actual code:

In the original workbook:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Run
"'CO2005AddIn.xla'!
EstimateWSEvents.PTWorksheet_SelectionChange"
,
Target
End Sub

In the Add-In's EstimateWSEvents module:
Sub PTWorksheet_SelectionChange(ByVal Target As Range)
UDAESTDLL.PTWorksheetSelectionChange Target
End Sub

In the DLL:
Public Sub PTWorksheetSelectionChange(ByVal Target As Range)
'some irrelevant code...
CategoryInfo.Show vbModal
end Sub

then the form comes up, the user types in the text box, and clicks
OK: In the OK_Click event handler of the CategoryInfo form:
Private Sub OK_Click()
ThisApp.ScreenUpdating = False

MsgBox "changing category name"

MsgBox ThisApp.EnableEvents 'this always comes up "True"

ThisApp.EnableEvents = True 'I set it just to make extra sure

'this line works
ThisWB.ActiveSheet.Range("D" & ThisApp.ActiveCell.row) =
NameTextBox.Text

'but this line is executed immediately after the previous one
(no
event generated)
MsgBox "Changed name in workbook. Now back in form."

Unload Me
End Sub

'Now, writing NameTextBox.Text should have made the execution go
straight to the worksheetchange event in the original workbook:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Run
"'CO2005AddIn.xla'!EstimateWSEvents.PTWorksheet_Ch ange",
Target
End Sub

'which would call the add-in:
In the Add-In's EstimateWSEvents module:
Sub PTWorksheet_Change(ByVal Target As Range)
UDAESTDLL.PTWorksheetChange Target
End Sub

'which would call the DLL and take an appropriate action.
But the code skips generating the event and just stays in the form.

I hope this clears things up for you, cool.
Thanks again for your help.


keepITcool wrote in message
. ..
What did the dialog say?

Be aware your testing the Activesheet of THISWORKBOOK.
(that's the WB in which the form resides.)

That could very well be different from the Activesheet in the
Activeworkbook


Else just zip and email to addr below.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Wexler) wrote:

cool,
Thanks for your suggestion. I put a breakpoint in the
worksheetchange event handler sub, and when I type in the cell
manually, it stops at the breakpoint. When I use the form, it
never hits the breakpoint. The same sheet is always active when
I use the form, and the value is written to the sheet, but no
event.... Any other ideas?

keepITcool wrote in message
. ..
put