I have a form that enables events, then changes a cell's value, but no
event is generated.

Private Sub OK_Click()
Application.EnableEvents = True

ThisWorkbook.ActiveSheet.Unprotect
ThisWorkbook.ActiveSheet.Range("C10").Value = "Hello"
ThisWorkbook.ActiveSheet.Protect

Unload Me
End Sub

What's going on?
The cell gets changed, but no execution at all in the
Worksheet_Change() event handler.

Without seeing the rest of your code it sounds like you moved the orde
of sheets after you implemented your code, does the event happen on an
of the sheets?

Try your code in a new workbook with only one sheet if it works then i
will be the order of your sheets, i had this problem once when i added
new sheet and then put it in alphabetical order all the other sheet
stopped working!

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=27102

Simon,
Thanks, but I don't move the sheets, and the same sheet is always
active when this form loads and unloads. The value went to the right
sheet, and right cell, but no event was fired...

Simon Lloyd wrote in message ...
Without seeing the rest of your code it sounds like you moved the order
of sheets after you implemented your code, does the event happen on any
of the sheets?

Try your code in a new workbook with only one sheet if it works then it
will be the order of your sheets, i had this problem once when i added a
new sheet and then put it in alphabetical order all the other sheets
stopped working!

Simon

put
OPTION EXPLICIT
at the top of the sheet's code module
then try Debug /Compile..

If no errors messages.. then at least the macro can run..

else try this in your code
it will tell you if THAT sheet has an event handler :)
(one-time exercise for testing only)


Private Sub OK_Click()
With ThisWorkbook
With ActiveSheet
.Unprotect
.Range("C10").Value = "Hello"
.Protect
End With
'Let's test...
On Error Resume Next
Dim s
s = Application.VBE.Version
If s = "" Then
MsgBox "No access allowed to VB Object"
Exit Sub
End If
On Error GoTo 0

Dim vbc, b, l&(3)

Set vbc = .VBProject.VBComponents(.ActiveSheet.CodeName).cod emodule
b = vbc.Find("Worksheet_Change(ByVal Target As Range)", _
l(0), l(1), l(2), l(3))
If b Then
MsgBox "Code found!.. in " & vbc.ProcOfLine(l(0), 0)
Else
Me.Hide
MsgBox "This sheet has no eventhandler"
vbc.CodePane.Show
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus
End If
End With

End Sub


keepITcool

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


(Wexler) wrote:

I have a form that enables events, then changes a cell's value, but no
event is generated.

Private Sub OK_Click()
Application.EnableEvents = True

ThisWorkbook.ActiveSheet.Unprotect
ThisWorkbook.ActiveSheet.Range("C10").Value = "Hello"
ThisWorkbook.ActiveSheet.Protect

Unload Me
End Sub

What's going on?
The cell gets changed, but no execution at all in the
Worksheet_Change() event handler.

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
OPTION EXPLICIT
at the top of the sheet's code module
then try Debug /Compile..

If no errors messages.. then at least the macro can run..

else try this in your code
it will tell you if THAT sheet has an event handler :)
(one-time exercise for testing only)


Private Sub OK_Click()
With ThisWorkbook
With ActiveSheet
.Unprotect
.Range("C10").Value = "Hello"
.Protect
End With
'Let's test...
On Error Resume Next
Dim s
s = Application.VBE.Version
If s = "" Then
MsgBox "No access allowed to VB Object"
Exit Sub
End If
On Error GoTo 0

Dim vbc, b, l&(3)

Set vbc = .VBProject.VBComponents(.ActiveSheet.CodeName).cod emodule
b = vbc.Find("Worksheet_Change(ByVal Target As Range)", _
l(0), l(1), l(2), l(3))
If b Then
MsgBox "Code found!.. in " & vbc.ProcOfLine(l(0), 0)
Else
Me.Hide
MsgBox "This sheet has no eventhandler"
vbc.CodePane.Show
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.SetFocus
End If
End With

End Sub


keepITcool

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


(Wexler) wrote:

I have a form that enables events, then changes a cell's value, but no
event is generated.

Private Sub OK_Click()
Application.EnableEvents = True

ThisWorkbook.ActiveSheet.Unprotect
ThisWorkbook.ActiveSheet.Range("C10").Value = "Hello"
ThisWorkbook.ActiveSheet.Protect

Unload Me
End Sub

What's going on?
The cell gets changed, but no execution at all in the
Worksheet_Change() event handler.

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

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_Se lectionChange",
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

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_Se lectionChange",
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