Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Howdy all
I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hi again Matt,
You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hey Peter
Yeah I know! But an Excel crash is not cool for any application so I had to find out the cause (wonder if it was the only cause?!). And I try to only come here for the hard questions! Some workstations here crash only intermittently , but some others I can get to crash on those steps without fail - nevertheless, obviously, the fact that any crash is quite a concern though. Probably obviously too, if I save and close and then reopen the file before viewing the print preview, Excel does not crash. Also, the initial recurrent problem occurs NOT in design mode by the way... Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? I tried the steps and for the workstations that crash every time, Excel crashed for me every time whether in design mode or not (although I didn't test this programmatically, just by pressing the design mode button myself). I'll try just hiding rather than deleting the (matrix of) checkboxes and also programmatically entering then exiting design mode after the offending code too. Thoughts? Cheers Matt "Peter T" <peter_t@discussions wrote in message ... Hi again Matt, You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
I'll try and look into this for my own purposes later, hadn't come accross
this issue with Print Preview. In the meantime: Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? Intuitively I would agree. But if this aids a reliable solution, after testing in every conceivable scenario, I guess it should be OK. I'll try just hiding rather than deleting the (matrix of) checkboxes If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Regards, Peter "Matt Jensen" wrote in message ... Hey Peter Yeah I know! But an Excel crash is not cool for any application so I had to find out the cause (wonder if it was the only cause?!). And I try to only come here for the hard questions! Some workstations here crash only intermittently , but some others I can get to crash on those steps without fail - nevertheless, obviously, the fact that any crash is quite a concern though. Probably obviously too, if I save and close and then reopen the file before viewing the print preview, Excel does not crash. Also, the initial recurrent problem occurs NOT in design mode by the way... Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? I tried the steps and for the workstations that crash every time, Excel crashed for me every time whether in design mode or not (although I didn't test this programmatically, just by pressing the design mode button myself). I'll try just hiding rather than deleting the (matrix of) checkboxes and also programmatically entering then exiting design mode after the offending code too. Thoughts? Cheers Matt "Peter T" <peter_t@discussions wrote in message ... Hi again Matt, You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Thanks Peter
If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it can be 4 or 7 columns wide), and I've been deleting the extra ones when resizing back to 4 from 7 but I guess the main reason for this was ultimately for file size reasons. But now that I rebuilt in 97 and the file size dropped by a 3rd though I guess hiding may suffice. I'll keep you posted on my trial and tribulations :-) If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Printing is sorted for OLEObjects thanks, although on this topic, how does one stop a row from printing do you know? - just hide the row? Or is there a 'nicer' way? Cheers Matt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hiding the checkbox and we're sorted...!!
Great. Actually, just remember one trick of the trade I was wondering if existed which'd be useful to know if it does... = I was wondering if, following this code: Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e if you could set something to Nothing or do some sort of clean up like that that might have 'cleared' Excel's memory or something and hence not cause it to crash on further operations such as Print Preview...? ? Thanks Matt "Peter T" <peter_t@discussions wrote in message ... I'll try and look into this for my own purposes later, hadn't come accross this issue with Print Preview. In the meantime: Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? Intuitively I would agree. But if this aids a reliable solution, after testing in every conceivable scenario, I guess it should be OK. I'll try just hiding rather than deleting the (matrix of) checkboxes If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Regards, Peter "Matt Jensen" wrote in message ... Hey Peter Yeah I know! But an Excel crash is not cool for any application so I had to find out the cause (wonder if it was the only cause?!). And I try to only come here for the hard questions! Some workstations here crash only intermittently , but some others I can get to crash on those steps without fail - nevertheless, obviously, the fact that any crash is quite a concern though. Probably obviously too, if I save and close and then reopen the file before viewing the print preview, Excel does not crash. Also, the initial recurrent problem occurs NOT in design mode by the way... Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? I tried the steps and for the workstations that crash every time, Excel crashed for me every time whether in design mode or not (although I didn't test this programmatically, just by pressing the design mode button myself). I'll try just hiding rather than deleting the (matrix of) checkboxes and also programmatically entering then exiting design mode after the offending code too. Thoughts? Cheers Matt "Peter T" <peter_t@discussions wrote in message ... Hi again Matt, You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hiding the checkbox and we're sorted...!!
Sorted on all workstations too btw. Matt |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Actually, just remember one trick of the trade I was wondering if existed
which'd be useful to know if it does... = I was wondering if, following this code: Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e if you could set something to Nothing or do some sort of clean up like that that might have 'cleared' Excel's memory or something and hence not cause it to crash on further operations such as Print Preview...? You can set an object variable = Nothing but not, AFAIK, an object itself. I tried following yesterday but it didn't make any difference (not surprisingly): Dim obOLE As OLEObject Set obOLE = Worksheets("Sheet1").OLEObjects("Checkbox1") obOLE.Delete Set obOLE = Nothing also, obOLE.PrintObject = False before the Delete doesn't seem to help. Earlier today I was getting intermittent crashes, you say as well. I assume this is due to some yet to be discovered consistent situation, rather than random. If so hopefully the issue can be resolved. Re your adjacent post I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it can be 4 or 7 columns wide), and I've been deleting the extra ones when resizing back to 4 from 7 but I guess the main reason for this was ultimately for file size reasons. But now that I rebuilt in 97 and the file size dropped by a 3rd though I guess hiding may suffice. I wouldn't think the difference of 3x30 controls would make a significant difference in file size, relative to what I assume is already a fairly large file. How are you trapping events for all these, individually or as a Class. Regards, Peter T "Matt Jensen" wrote in message ... Hiding the checkbox and we're sorted...!! Great. Actually, just remember one trick of the trade I was wondering if existed which'd be useful to know if it does... = I was wondering if, following this code: Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e if you could set something to Nothing or do some sort of clean up like that that might have 'cleared' Excel's memory or something and hence not cause it to crash on further operations such as Print Preview...? ? Thanks Matt "Peter T" <peter_t@discussions wrote in message ... I'll try and look into this for my own purposes later, hadn't come accross this issue with Print Preview. In the meantime: Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? Intuitively I would agree. But if this aids a reliable solution, after testing in every conceivable scenario, I guess it should be OK. I'll try just hiding rather than deleting the (matrix of) checkboxes If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Regards, Peter "Matt Jensen" wrote in message ... Hey Peter Yeah I know! But an Excel crash is not cool for any application so I had to find out the cause (wonder if it was the only cause?!). And I try to only come here for the hard questions! Some workstations here crash only intermittently , but some others I can get to crash on those steps without fail - nevertheless, obviously, the fact that any crash is quite a concern though. Probably obviously too, if I save and close and then reopen the file before viewing the print preview, Excel does not crash. Also, the initial recurrent problem occurs NOT in design mode by the way... Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? I tried the steps and for the workstations that crash every time, Excel crashed for me every time whether in design mode or not (although I didn't test this programmatically, just by pressing the design mode button myself). I'll try just hiding rather than deleting the (matrix of) checkboxes and also programmatically entering then exiting design mode after the offending code too. Thoughts? Cheers Matt "Peter T" <peter_t@discussions wrote in message ... Hi again Matt, You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Forgot about this when just replying:
Printing is sorted for OLEObjects thanks, although on this topic, how does one stop a row from printing do you know? - just hide the row? Or is there a 'nicer' way? You can format font white & no fill, reset when done. It might mean first paste-special formats to a hidden sheet and back when done. Wouldn't say that's a "nicer way" but it preserves that overall layout. Regards, Peter T "Matt Jensen" wrote in message ... Thanks Peter If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it can be 4 or 7 columns wide), and I've been deleting the extra ones when resizing back to 4 from 7 but I guess the main reason for this was ultimately for file size reasons. But now that I rebuilt in 97 and the file size dropped by a 3rd though I guess hiding may suffice. I'll keep you posted on my trial and tribulations :-) If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Printing is sorted for OLEObjects thanks, although on this topic, how does one stop a row from printing do you know? - just hide the row? Or is there a 'nicer' way? Cheers Matt |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hey Peter
Re checkbox events, I don't really use them, I've just got them linked to a corresponding row/column in a separate sheet which stores their value, no need for events luckily. Apart from the fact that I'm not competent enough to create classes yet. I don't really understand them actually - I do but I haven't seen an use for them yet, although if I understood them more maybe I would use them more often...! The file is only 550KB or so at the moment after I rebuilt in 97 (was 1.5MB), although it seems to increase in file size with every save, even it you just open then save then close...! On a slightly different but related note, how do prevent printing of cell data - is there a row.PrintObject = False method or something, or do I have to hide/change colours etc. or something? Matt "Peter T" <peter_t@discussions wrote in message ... Actually, just remember one trick of the trade I was wondering if existed which'd be useful to know if it does... = I was wondering if, following this code: Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e if you could set something to Nothing or do some sort of clean up like that that might have 'cleared' Excel's memory or something and hence not cause it to crash on further operations such as Print Preview...? You can set an object variable = Nothing but not, AFAIK, an object itself. I tried following yesterday but it didn't make any difference (not surprisingly): Dim obOLE As OLEObject Set obOLE = Worksheets("Sheet1").OLEObjects("Checkbox1") obOLE.Delete Set obOLE = Nothing also, obOLE.PrintObject = False before the Delete doesn't seem to help. Earlier today I was getting intermittent crashes, you say as well. I assume this is due to some yet to be discovered consistent situation, rather than random. If so hopefully the issue can be resolved. Re your adjacent post I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it can be 4 or 7 columns wide), and I've been deleting the extra ones when resizing back to 4 from 7 but I guess the main reason for this was ultimately for file size reasons. But now that I rebuilt in 97 and the file size dropped by a 3rd though I guess hiding may suffice. I wouldn't think the difference of 3x30 controls would make a significant difference in file size, relative to what I assume is already a fairly large file. How are you trapping events for all these, individually or as a Class. Regards, Peter T "Matt Jensen" wrote in message ... Hiding the checkbox and we're sorted...!! Great. Actually, just remember one trick of the trade I was wondering if existed which'd be useful to know if it does... = I was wondering if, following this code: Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e if you could set something to Nothing or do some sort of clean up like that that might have 'cleared' Excel's memory or something and hence not cause it to crash on further operations such as Print Preview...? ? Thanks Matt "Peter T" <peter_t@discussions wrote in message ... I'll try and look into this for my own purposes later, hadn't come accross this issue with Print Preview. In the meantime: Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? Intuitively I would agree. But if this aids a reliable solution, after testing in every conceivable scenario, I guess it should be OK. I'll try just hiding rather than deleting the (matrix of) checkboxes If you are repeatedly creating and deleting similar OLEobjects I would definately adopt the approach of hiding when needed. I think this is better practice for all versions regardless of the current problem in xl97. Same goes for any type of object, including shapes. If your reason for hiding is merely to avoid printing, maybe: MySheet.OLEObjects.PrintObject = False Regards, Peter "Matt Jensen" wrote in message ... Hey Peter Yeah I know! But an Excel crash is not cool for any application so I had to find out the cause (wonder if it was the only cause?!). And I try to only come here for the hard questions! Some workstations here crash only intermittently , but some others I can get to crash on those steps without fail - nevertheless, obviously, the fact that any crash is quite a concern though. Probably obviously too, if I save and close and then reopen the file before viewing the print preview, Excel does not crash. Also, the initial recurrent problem occurs NOT in design mode by the way... Also, I must admit, I'm rather dubious about programming an application to enter/exit design mode - it just seems wrong...! Thoughts? I tried the steps and for the workstations that crash every time, Excel crashed for me every time whether in design mode or not (although I didn't test this programmatically, just by pressing the design mode button myself). I'll try just hiding rather than deleting the (matrix of) checkboxes and also programmatically entering then exiting design mode after the offending code too. Thoughts? Cheers Matt "Peter T" <peter_t@discussions wrote in message ... Hi again Matt, You do keep coming up with some obscure things <g. Following your instructions I replicated your crash in xl97, several times. Then I exited design mode and no more problems, I mean in further testing no crashes no matter what state design mode - weird - yet again with your topics! In our previous conversation I said some things relating to controls in xl97 are best done in design mode. Though it turned out not relevant to that issue I stand by it as a generalised statement. But now perhaps I should add some things are better NOT done in design mode. I can no longer replicate the problem / solution but try folowing (new session's of windows etc): If Application.CommandBars("Exit Design Mode").Controls(1).State = msoButtonDown Then Application.CommandBars("Exit Design Mode").Controls(1).Execute End If Regards, Peter T "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
I've been able to narrow it down even further would you believe (by
accident) 1) New workbook 2) type 'text' in cell a1 of sheet1 3) on sheet1, insert any Control Toolbar checkbox (happens for some other controls too), then delete it immediately Print Preview = Kaboom!! :-) Matt "Matt Jensen" wrote in message ... Howdy all I've been having a problem with Excel 97 crashing that I've been able to narrow down through a process of elimination to the following cause (there is no problem on WinXP Excel 2002 by the way): Steps to produce a crash: 1) Create new workbook 2) Insert a Control Toolbar Checkbox on Sheet1. 3) In any cell on Sheet1, put something in a cell (so that when you choose the Excel menu option 'Print Preview' it won't say 'nothing to print' and will attempt to show a print preview) eg. type the letters 'text' in cell A1 of Sheet1. 4) Go to VBE, create new module, enter this code: Sub ExampleCrash() Worksheets("Sheet1").OLEObjects("Checkbox1").Delet e End Sub 5) Run Macro 6) Return to Excel 7) Hit Print Preview Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2 on WinNT. It is a large organisation where I'm working - 100s to 1000s of users with same PC setup (so therefore I have no control over what my users will be running). Firstly, does this cause Excel 97 to crash for anyone else? Secondly, what is the problem? Is my code the problem - if so, how can I change it to stop Excel crashing? Any suggestions as to how I can change the code to still achieve the same effect but not crash Excel? Eg. maybe force a save after the macro or some other trick of the trade / "workaround" that might prevent Excel from crashing? Thanks a lot for any help or ideas - I'm not sure where to start!! Cheers Matt |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
"Matt Jensen" wrote in message
... Hey Peter Re checkbox events, I don't really use them, I've just got them linked to a corresponding row/column in a separate sheet which stores their value, no need for events luckily. Apart from the fact that I'm not competent enough to create classes yet. I don't really understand them actually - I do but I haven't seen an use for them yet, although if I understood them more maybe I would use them more often...! Sure, if the value of the linked cell is used only in worksheet formulas you wouldn't need events. Stephen Bullen recently posted an example of trapping events of similar controls in a class he Subject: "running same code with multiple controls" Jan 2005 http://tinyurl.com/4jp2v Stephen's example was with Labels but easily changed to Checkboxes. In the class click event could do something like: Set rng = Range(With-events-variable-name-of-checkbox.LinkedCell) and then do all sorts of things. The file is only 550KB or so at the moment after I rebuilt in 97 (was 1.5MB), although it seems to increase in file size with every save, even it you just open then save then close...! This could be due to reworking your code. Rob Bovey's Code Cleaner is universally recommended: http://www.appspro.com/ On a slightly different but related note, how do prevent printing of cell data - is there a row.PrintObject = False method or something, or do I have to hide/change colours etc. or something? I posted some comments yesterday to another part of the thread. Regards, Peter T |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Hey Peter
Thanks for this. Been thinking about this some more, think my understanding of classes is slowly improving - effectively, one is 'class'ifying a group of elements with the intent to treat, particularly events, them all as doing the same thing - is this right? Part of my inability to understand classes I think is due to the fact that I thought that surely when say a control toolbox checkbox event occurred that you could call a procedure and the procedure would be able to know, among other things, the name of the checkbox calling it eg. by using 'this.' or 'me.' but from what I can tell this is not the case with VBA? However, I guess I can see the advantage of "classing" elements as part of the same class and associating particular events with this class, and hence modularising application objects and keeping them distinctly separate from others. Is this understanding right? Matt "Peter T" <peter_t@discussions wrote in message ... "Matt Jensen" wrote in message ... Hey Peter Re checkbox events, I don't really use them, I've just got them linked to a corresponding row/column in a separate sheet which stores their value, no need for events luckily. Apart from the fact that I'm not competent enough to create classes yet. I don't really understand them actually - I do but I haven't seen an use for them yet, although if I understood them more maybe I would use them more often...! Sure, if the value of the linked cell is used only in worksheet formulas you wouldn't need events. Stephen Bullen recently posted an example of trapping events of similar controls in a class he Subject: "running same code with multiple controls" Jan 2005 http://tinyurl.com/4jp2v Stephen's example was with Labels but easily changed to Checkboxes. In the class click event could do something like: Set rng = Range(With-events-variable-name-of-checkbox.LinkedCell) and then do all sorts of things. The file is only 550KB or so at the moment after I rebuilt in 97 (was 1.5MB), although it seems to increase in file size with every save, even it you just open then save then close...! This could be due to reworking your code. Rob Bovey's Code Cleaner is universally recommended: http://www.appspro.com/ On a slightly different but related note, how do prevent printing of cell data - is there a row.PrintObject = False method or something, or do I have to hide/change colours etc. or something? I posted some comments yesterday to another part of the thread. Regards, Peter T |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
Matt Jensen wrote: Part of my inability to understand classes I think is due to the fact that I thought that surely when say a control toolbox checkbox event occurred that you could call a procedure and the procedure would be able to know, among other things, the name of the checkbox calling it eg. by using 'this.' or 'me.' but from what I can tell this is not the case with VBA? VBA does indeed have the keyword Me to refer internally to the current instance of the class. However, Me.Name would call the class's Name property/method, assuming it exists and is declared as Public. If you are sinking a worksheet control, you would call the Name property on the object variable which points to the control e.g. ' --- <Class code Option Explicit Private WithEvents m_Checkbox As MSForms.Checkbox Public Function Init( _ ByVal Checkbox As MSForms.Checkbox _ ) As Boolean Set m_Checkbox = Checkbox Init = True End Function Public Property Get Name() As String Name = "Matt Jensen" End Property Private Sub m_Checkbox_Click() MsgBox "You clicked " & m_Checkbox.Name, , Me.Name End Sub ' --- </Class code Been thinking about this some more, think my understanding of classes is slowly improving - effectively, one is 'class'ifying a group of elements with the intent to treat, particularly events, them all as doing the same thing I guess I can see the advantage of "classing" elements as part of the same class and associating particular events with this class, and hence modularising application objects and keeping them distinctly separate from others. Is this understanding right? It's hard to know if I've correctly understood your understanding <g but you are definitely thinking along the right lines. Jamie. -- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that Crashes Excel without fail - Excel 97 SR2 WinNT
You gave a better intro to class'ing control events than I could have
written, had you not said otherwise I would assume you already an expert! You hinted at not being quite sure about how to know which control is triggering the event, I'm no expert on classes (certainly not in the company of Jamie C) but maybe this nonsense example will demonstrate a few things: Manually create three checkboxes on Sheet1 with linked-cells A1, A2 & A3 (don't cover cell E10) Code in a class named clsCheckBoxEvnts and normal module as indicated - ''start Class named clsCheckBoxEvnts Option Explicit Public WithEvents ChBox As MSForms.CheckBox Private appVer As Long Private sTrick As String Private Sub ChBox_Change() Dim rng As Range, cx As Long, s As String, i As Long With ChBox Set rng = .Parent.Range(.LinkedCell) _ .Offset(0, 1).Resize(1, 4) If .Value Then .Caption = .Index & " " & MyString cx = .Index + 24 Else .Caption = .Name cx = xlNone End If If gcVars.some_var = 8 Then xl97fix rng.Interior.ColorIndex = cx For i = 1 To UBound(gaCBs) If gaCBs(i).ChBox.Value Then 'compare ".Caption = MyString" above and next line s = s & gaCBs(i).MyString & " " End If Next If s = "" Then s = gcVars.MyString .Parent.Range("e10") = s End With End Sub Public Property Let MyString(str As String) sTrick = str End Property Property Get MyString() As String MyString = sTrick End Property Public Property Let some_var(n As Long) appVer = n End Property Property Get some_var() As Long some_var = appVer End Property Private Sub xl97fix() 'in xl97, if checkbox has focus can error 'if changing cell formats (but not values), very odd! On Error GoTo done 'prevent any selection change events Application.EnableEvents = False If Intersect(Windows(1).VisibleRange, ActiveCell) Is Nothing Then Windows(1).VisibleRange(1, 1).Activate Else ActiveCell.Activate End If done: Application.EnableEvents = True End Sub Private Sub Class_Terminate() ChBox.Enabled = False End Sub ''end code in clsCheckBoxEvnts ''''''''''''''''''''''''''''''''''''''''''''''' ''start code in normal module Option Explicit Public gaCBs() As New clsCheckBoxEvnts Public gcVars As New clsCheckBoxEvnts Sub Setup() 'called by [say] wb-activate Dim i As Long Dim obOLE As OLEObject Dim va va = Array("Rabbit", "Hat", "Magic") For Each obOLE In Worksheets("Sheet1").OLEObjects If TypeOf obOLE.Object Is MSForms.CheckBox Then i = i + 1 obOLE.Object.Enabled = True ReDim Preserve gaCBs(1 To i) Set gaCBs(i).ChBox = obOLE.Object gaCBs(i).MyVar = va(i - 1) End If Next gcVars.MyString = "No tricks" gcVars.some_var = CLng(Val(Application.Version)) setCBoxes False End Sub Private Sub setCBoxes(bVal As Boolean) Dim ob As Object, i As Long For i = 1 To UBound(gaCBs) gaCBs(i).ChBox.Value = bVal Next End Sub Sub Clearup() 'called by say wb deactivate Erase gaCBs End Sub ''end code in normal module In real life code I can't imagine ever using the "array" method of holding the controls with an unknown qty using redim preserve. Typically the array method is used fully dimensioned to hold a known number of similar controls on a userform. Use the "collection" method (see previous link to Stephen Bullen's example). Also, wouldn't store the app-version variable this way. As you've had so many problems with controls in xl97 look at the "xl97fix" routine, do you find you need it? Regards, Peter T "Matt Jensen" wrote in message ... Hey Peter Thanks for this. Been thinking about this some more, think my understanding of classes is slowly improving - effectively, one is 'class'ifying a group of elements with the intent to treat, particularly events, them all as doing the same thing - is this right? Part of my inability to understand classes I think is due to the fact that I thought that surely when say a control toolbox checkbox event occurred that you could call a procedure and the procedure would be able to know, among other things, the name of the checkbox calling it eg. by using 'this.' or 'me.' but from what I can tell this is not the case with VBA? However, I guess I can see the advantage of "classing" elements as part of the same class and associating particular events with this class, and hence modularising application objects and keeping them distinctly separate from others. Is this understanding right? Matt "Peter T" <peter_t@discussions wrote in message ... "Matt Jensen" wrote in message ... Hey Peter Re checkbox events, I don't really use them, I've just got them linked to a corresponding row/column in a separate sheet which stores their value, no need for events luckily. Apart from the fact that I'm not competent enough to create classes yet. I don't really understand them actually - I do but I haven't seen an use for them yet, although if I understood them more maybe I would use them more often...! Sure, if the value of the linked cell is used only in worksheet formulas you wouldn't need events. Stephen Bullen recently posted an example of trapping events of similar controls in a class he Subject: "running same code with multiple controls" Jan 2005 http://tinyurl.com/4jp2v Stephen's example was with Labels but easily changed to Checkboxes. In the class click event could do something like: Set rng = Range(With-events-variable-name-of-checkbox.LinkedCell) and then do all sorts of things. The file is only 550KB or so at the moment after I rebuilt in 97 (was 1.5MB), although it seems to increase in file size with every save, even it you just open then save then close...! This could be due to reworking your code. Rob Bovey's Code Cleaner is universally recommended: http://www.appspro.com/ On a slightly different but related note, how do prevent printing of cell data - is there a row.PrintObject = False method or something, or do I have to hide/change colours etc. or something? I posted some comments yesterday to another part of the thread. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Simple code crashes Excel | Excel Programming | |||
VBA File in EXCEL 2000 Crashes in code that I have made no changes | Excel Programming | |||
why doesn't excel97 winNT vba macro code work in excel2002 winXP??? | Excel Programming | |||
Code in ThisWorkbook crashes Excel | Excel Programming |