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

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

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

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

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

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

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

Hiding the checkbox and we're sorted...!!

Sorted on all workstations too btw.
Matt

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

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