| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet change event not firing
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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_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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
cool,
Thanks so much for your advice on WithEvents. I use it in the DLL, and now it catches all the events directly from the xls. It even follows through with the worksheet change event from throught the form! This will make the design much cleaner and easier to handle! 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_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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Worksheet change event not firing
Great.
where can I send the invoice ? <g keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool (Wexler) wrote: cool, Thanks so much for your advice on WithEvents. I use it in the DLL, and now it catches all the events directly from the xls. It even follows through with the worksheet change event from throught the form! This will make the design much cleaner and easier to handle! keepITcool wrote in message . .. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet Change Event | Excel Discussion (Misc queries) | |||
| Workbook open event not firing (both 2000 and XP) | Excel Programming | |||
| Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
| Workbook.Open Event Procedure not firing | Excel Programming | |||
| OnTime event not firing in Workbook_Open event procedure | Excel Programming | |||
Linear Mode
