Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I
think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
joeeng wrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
I am also using SP2. I have also experienced this bug in Excel 2002 and I
had hoped that upgrading to Excel 2003 would fix the problem. I have created a small Excel workbook that demonstrates the issue. I can email it to you for evaluation. "semiopen" wrote: joeeng wrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
joeeng wrote: I am also using SP2. I have also experienced this bug in Excel 2002 and I had hoped that upgrading to Excel 2003 would fix the problem. I have created a small Excel workbook that demonstrates the issue. I can email it to you for evaluation. Well, I'm no expert, but I'm curious enough to give it a look - so go ahead and e-mail it. "semiopen" wrote: joeeng wrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
I have been able to narrow down the source of this bug. "If" testing for the
target intersect in the Worksheet_Change subroutine triggered by the ActiveX button which calls a Userform containing a TextBox Control containing a ControlSource property located on the Worksheet with the ActiveX button. Press the ActiveX button, change the value in the TextBox, close the Userform, save the Workbook. When the workbook is re-opened and the Workbook_Open subroutine has a Worksheets().Select or Worksheets().Activate or Worksheets().Range().Copy for the worksheet containing the ActiveX button, a 57121 run-time error occurs every time. Delete the ActiveX button or Rem out the "If" test for the target intersect in the Worksheet_Change subroutine and the error goes away. (By the way, the "If" testing is set to allow the change made in the ControlSource property location). "semiopen" wrote: joeeng wrote: I am also using SP2. I have also experienced this bug in Excel 2002 and I had hoped that upgrading to Excel 2003 would fix the problem. I have created a small Excel workbook that demonstrates the issue. I can email it to you for evaluation. Well, I'm no expert, but I'm curious enough to give it a look - so go ahead and e-mail it. "semiopen" wrote: joeeng wrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
On Jan 5, 10:27 am, joeeng wrote:
Here's what I have been able to find and a work around after much investigation and frustration. Microsoft does not seem to admit that this is a problem, but indeed it is. I have discovered that the problem only presents itself (and not always) when the workbook is closed while a worksheet containing an ActiveX control (particularly buttons and scrollbars) is selected/active and only after the ActiveX has been used while the worksheet is active. Therefore, the workaround was to use the Workbook_BeforeClose routine in the ThisWorkbook module to activate a worksheet that does not contain an ActiveX control before the Workbook closes. This has solved the problem for me. Hope this is helpful to you. "Mike Jerakis" wrote: Did you have any luck with this? I have been having problems with _Change events and major lockup errors intermittently and was hoping for another avenue. "joeeng" wrote: I have been able to narrow down the source of this bug. "If" testing for the target intersect in the Worksheet_Change subroutine triggered by the ActiveX button which calls a Userform containing a TextBox Control containing a ControlSource property located on the Worksheet with the ActiveX button. Press the ActiveX button, change the value in the TextBox, close the Userform, save the Workbook. When the workbook is re-opened and the Workbook_Open subroutine has a Worksheets().Select or Worksheets().Activate or Worksheets().Range().Copy for the worksheet containing the ActiveX button, a 57121 run-time error occurs every time. Delete the ActiveX button or Rem out the "If" test for the target intersect in the Worksheet_Change subroutine and the error goes away. (By the way, the "If" testing is set to allow the change made in the ControlSource property location). "semiopen" wrote: joeengwrote: I am also using SP2. I have also experienced this bug in Excel 2002 and I had hoped that upgrading to Excel 2003 would fix the problem. I have created a small Excel workbook that demonstrates the issue. I can email it to you for evaluation. Well, I'm no expert, but I'm curious enough to give it a look - so go ahead and e-mail it. "semiopen" wrote: joeengwrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help.- Hide quoted text - - Show quoted text - joeeng i had a similar problem which was solved by installing Office 2003 SP2. Whjen you say you have SP2, do you mean XP SP2 or Office 2003 SP2. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2003 involving ActiveX Controls on worksheets
I am using Office 2003 SP2. I have been using the workaround so I cannot say
for sure that the problem has not been addressed in Office 2003. Although, I might add that there are some incompatibilities between Office 2002 and Office 2003. I had to make some vba code changes to correct new issues when I upgraded to Office 2003. "suputnic" wrote: On Jan 5, 10:27 am, joeeng wrote: Here's what I have been able to find and a work around after much investigation and frustration. Microsoft does not seem to admit that this is a problem, but indeed it is. I have discovered that the problem only presents itself (and not always) when the workbook is closed while a worksheet containing an ActiveX control (particularly buttons and scrollbars) is selected/active and only after the ActiveX has been used while the worksheet is active. Therefore, the workaround was to use the Workbook_BeforeClose routine in the ThisWorkbook module to activate a worksheet that does not contain an ActiveX control before the Workbook closes. This has solved the problem for me. Hope this is helpful to you. "Mike Jerakis" wrote: Did you have any luck with this? I have been having problems with _Change events and major lockup errors intermittently and was hoping for another avenue. "joeeng" wrote: I have been able to narrow down the source of this bug. "If" testing for the target intersect in the Worksheet_Change subroutine triggered by the ActiveX button which calls a Userform containing a TextBox Control containing a ControlSource property located on the Worksheet with the ActiveX button. Press the ActiveX button, change the value in the TextBox, close the Userform, save the Workbook. When the workbook is re-opened and the Workbook_Open subroutine has a Worksheets().Select or Worksheets().Activate or Worksheets().Range().Copy for the worksheet containing the ActiveX button, a 57121 run-time error occurs every time. Delete the ActiveX button or Rem out the "If" test for the target intersect in the Worksheet_Change subroutine and the error goes away. (By the way, the "If" testing is set to allow the change made in the ControlSource property location). "semiopen" wrote: joeengwrote: I am also using SP2. I have also experienced this bug in Excel 2002 and I had hoped that upgrading to Excel 2003 would fix the problem. I have created a small Excel workbook that demonstrates the issue. I can email it to you for evaluation. Well, I'm no expert, but I'm curious enough to give it a look - so go ahead and e-mail it. "semiopen" wrote: joeengwrote: I have discovered that there is a bug in Excel 2003 (and Excel 2002), which I think has propagated since Excel 97. I have found references to this bug for Excel 97 (with workarounds identified), but the bug still exists and worse, the workarounds identified for Excel 97 do not work. Here is what I have discovered: Place an ActiveX button on a worksheet with vba click code supporting it. Activate the control (press the button). Save the workbook before activating any other worksheet (or workbook) and before manually selecting any cell on the worksheet. With vba code in the Workbook_Open subroutine that either selects or activates the worksheet that contains the ActiveX button, re-open the workbook. Excel crashes with a 32809 run-time error at the line that selects or activates the worksheet (or any range on the worksheet) with the ActiveX button. Less frequently, it may crash with a 57121 run-time error at the same point. Excel 2003 seems to corrupt the vba code somehow, because Excel 2003 does not recover from this error (even by resetting vba execution) until the workbook is closed, opened with macros disabled, saved, and then re-opened. The workarounds identified for Excel 97 of setting the "TakeFocusOnClick" for the ActiveX button to false does not help in Excel 2003. The error does not show up if any manual selection is made prior to closing the workbook after pressing the ActiveX button. Does anyone have knowledge of this problem or comments? Despite your fairly clear description, I have been unable to replicate the bug. I have SP2 - maybe you have a slightly earlier release. Does the error pop up every time you go through a save-reopen cycle (assuming the last thing you did was press the button), or does it only occur the first time? Maybe selecting a cell and/or changing the active sheet in the beforesave or beforeclose event might help.- Hide quoted text - - Show quoted text - joeeng i had a similar problem which was solved by installing Office 2003 SP2. Whjen you say you have SP2, do you mean XP SP2 or Office 2003 SP2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No ActiveX controls when reading 2007 file formats into Excel 2003 | Excel Discussion (Misc queries) | |||
Activex controls in Excel 2003 | Excel Worksheet Functions | |||
Help with Excel ActiveX listbox controls | Excel Discussion (Misc queries) | |||
Tab between ActiveX controls on an Excel worksheet | Excel Programming | |||
Deleting worksheets containing ActiveX controls | Excel Programming |