I have been jointly developing a form with a college and she started getting
€œMicrosoft Office Excel has encountered a problem and needs to close. We are
sorry for the inconvenience.€� In other words it crashed with no errors or
indication of what the problem might be.

If you disable macros it comes up fine. I then stopped the Auto_Open sub
running by simply making the first line an Exit Sub, saved it, re-ran the
Auto_Open sub routine from VBE and it works fine. Save the file and re-run it
works fine. Re-start, exit without saving and it crashes again on re-start. I
also get intermittent €œAutomation Error€� normally when trying to save the
file. The other error I get is €œNot enough system resources to display
completely€�. I have tried deleting some of the form content and some of the
code and you think you have fixed it, you save it, re-start it and it works
fine. Then you make a change, which could be as simple a typing a character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of memory
and my college only has 512MB, but I have 2GB. I also found a note about
zooming to 100%, and this appeared to work i.e. changing two sheets from 80%
to 100%. Then made a simple change (cell update) and I am back to where I
started.

There are only 250 lines of very basic code, but there are a lot of userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Step through the macro and find the point that immediately precedes the
crash. Hopefully you can reliably reproduce the error and work around the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to close. We
are
sorry for the inconvenience." In other words it crashed with no errors or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the Auto_Open sub
running by simply making the first line an Exit Sub, saved it, re-ran the
Auto_Open sub routine from VBE and it works fine. Save the file and re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to save the
file. The other error I get is "Not enough system resources to display
completely". I have tried deleting some of the form content and some of
the
code and you think you have fixed it, you save it, re-start it and it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of memory
and my college only has 512MB, but I have 2GB. I also found a note about
zooming to 100%, and this appeared to work i.e. changing two sheets from
80%
to 100%. Then made a simple change (cell update) and I am back to where I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes the
crash. Hopefully you can reliably reproduce the error and work around the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to close. We
are
sorry for the inconvenience." In other words it crashed with no errors or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the Auto_Open sub
running by simply making the first line an Exit Sub, saved it, re-ran the
Auto_Open sub routine from VBE and it works fine. Save the file and re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to save the
file. The other error I get is "Not enough system resources to display
completely". I have tried deleting some of the form content and some of
the
code and you think you have fixed it, you save it, re-start it and it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of memory
and my college only has 512MB, but I have 2GB. I also found a note about
zooming to 100%, and this appeared to work i.e. changing two sheets from
80%
to 100%. Then made a simple change (cell update) and I am back to where I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes the
crash. Hopefully you can reliably reproduce the error and work around the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to close.
We
are
sorry for the inconvenience." In other words it crashed with no errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the Auto_Open
sub
running by simply making the first line an Exit Sub, saved it, re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file and
re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to save
the
file. The other error I get is "Not enough system resources to display
completely". I have tried deleting some of the form content and some of
the
code and you think you have fixed it, you save it, re-start it and it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of
memory
and my college only has 512MB, but I have 2GB. I also found a note
about
zooming to 100%, and this appeared to work i.e. changing two sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back to where
I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

bound data? If you mean have I used RowSource and ControlSource to link the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes the
crash. Hopefully you can reliably reproduce the error and work around the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to close.
We
are
sorry for the inconvenience." In other words it crashed with no errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the Auto_Open
sub
running by simply making the first line an Exit Sub, saved it, re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file and
re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to save
the
file. The other error I get is "Not enough system resources to display
completely". I have tried deleting some of the form content and some of
the
code and you think you have fixed it, you save it, re-start it and it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of
memory
and my college only has 512MB, but I have 2GB. I also found a note
about
zooming to 100%, and this appeared to work i.e. changing two sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back to where
I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Maybe. The reason I ask is because something might be triggering recursion,
where a change in a cell triggers calculation and/or the control's change
event, which triggers calculation, which triggers the change event, etc. If
you remove all bound data instances in all of the controls, perhaps even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to Manual and
try to replicate the problem. (Calc to Manual, save workbook, close, open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes
the
crash. Hopefully you can reliably reproduce the error and work around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to
close.
We
are
sorry for the inconvenience." In other words it crashed with no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file and
re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to
save
the
file. The other error I get is "Not enough system resources to
display
completely". I have tried deleting some of the form content and some
of
the
code and you think you have fixed it, you save it, re-start it and
it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of
memory
and my college only has 512MB, but I have 2GB. I also found a note
about
zooming to 100%, and this appeared to work i.e. changing two sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back to
where
I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Tim,

By removing all of the Forms ControlSource and RowSource values, it fixed
the problem, so now I guess I will have to right some code to load them with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering recursion,
where a change in a cell triggers calculation and/or the control's change
event, which triggers calculation, which triggers the change event, etc. If
you remove all bound data instances in all of the controls, perhaps even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to Manual and
try to replicate the problem. (Calc to Manual, save workbook, close, open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes
the
crash. Hopefully you can reliably reproduce the error and work around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to
close.
We
are
sorry for the inconvenience." In other words it crashed with no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file and
re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to
save
the
file. The other error I get is "Not enough system resources to
display
completely". I have tried deleting some of the form content and some
of
the
code and you think you have fixed it, you save it, re-start it and
it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of
memory
and my college only has 512MB, but I have 2GB. I also found a note
about
zooming to 100%, and this appeared to work i.e. changing two sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back to
where
I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Not fixed! All references to ControlSource and Row source have been removed
from the forms, but it still crashes on start up, but never from the VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart, then I
have to change data in any of the form fields, then save, then exit, then
restart and it crashes. Having to go through this loop every time I wish to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it fixed
the problem, so now I guess I will have to right some code to load them with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering recursion,
where a change in a cell triggers calculation and/or the control's change
event, which triggers calculation, which triggers the change event, etc. If
you remove all bound data instances in all of the controls, perhaps even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to Manual and
try to replicate the problem. (Calc to Manual, save workbook, close, open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately precedes
the
crash. Hopefully you can reliably reproduce the error and work around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she started
getting
"Microsoft Office Excel has encountered a problem and needs to
close.
We
are
sorry for the inconvenience." In other words it crashed with no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file and
re-run
it
works fine. Re-start, exit without saving and it crashes again on
re-start. I
also get intermittent "Automation Error" normally when trying to
save
the
file. The other error I get is "Not enough system resources to
display
completely". I have tried deleting some of the form content and some
of
the
code and you think you have fixed it, you save it, re-start it and
it
works
fine. Then you make a change, which could be as simple a typing a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of
memory
and my college only has 512MB, but I have 2GB. I also found a note
about
zooming to 100%, and this appeared to work i.e. changing two sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back to
where
I
started.

There are only 250 lines of very basic code, but there are a lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

I know the type of debugging difficulties this can pose. A similar situation
occurred to me at one time, requiring workbook close/reopens, incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time. Once
you can do that, it is much easier to localize the problem and fix it.




--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Not fixed! All references to ControlSource and Row source have been
removed
from the forms, but it still crashes on start up, but never from the VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not
much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart, then I
have to change data in any of the form fields, then save, then exit, then
restart and it crashes. Having to go through this loop every time I wish
to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and
then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it fixed
the problem, so now I guess I will have to right some code to load them
with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering
recursion,
where a change in a cell triggers calculation and/or the control's
change
event, which triggers calculation, which triggers the change event,
etc. If
you remove all bound data instances in all of the controls, perhaps
even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to
Manual and
try to replicate the problem. (Calc to Manual, save workbook, close,
open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to
link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using
RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you
step
through! AND you only get the other errors when you are trying to
save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately
precedes
the
crash. Hopefully you can reliably reproduce the error and work
around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she
started
getting
"Microsoft Office Excel has encountered a problem and needs to
close.
We
are
sorry for the inconvenience." In other words it crashed with no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file
and
re-run
it
works fine. Re-start, exit without saving and it crashes again
on
re-start. I
also get intermittent "Automation Error" normally when trying
to
save
the
file. The other error I get is "Not enough system resources to
display
completely". I have tried deleting some of the form content and
some
of
the
code and you think you have fixed it, you save it, re-start it
and
it
works
fine. Then you make a change, which could be as simple a typing
a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running
out of
memory
and my college only has 512MB, but I have 2GB. I also found a
note
about
zooming to 100%, and this appeared to work i.e. changing two
sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back
to
where
I
started.

There are only 250 lines of very basic code, but there are a
lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Tim,

I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.


After removing all the controlsource code this is the first line on a macro
to load a value back to the form:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

With this line in a Module (not in the form), it seems to consistanty crash
when started from the cmd line. When run in VBE it's fine.

I am still testing by slowly adding back in my code so will post another
update good or bad.

Thanks for the continued encouragement, cos I'm about to throw this excel
app in the bin!

--
Trefor


"Tim Zych" wrote:

I know the type of debugging difficulties this can pose. A similar situation
occurred to me at one time, requiring workbook close/reopens, incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time. Once
you can do that, it is much easier to localize the problem and fix it.




--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Not fixed! All references to ControlSource and Row source have been
removed
from the forms, but it still crashes on start up, but never from the VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not
much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart, then I
have to change data in any of the form fields, then save, then exit, then
restart and it crashes. Having to go through this loop every time I wish
to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and
then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it fixed
the problem, so now I guess I will have to right some code to load them
with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering
recursion,
where a change in a cell triggers calculation and/or the control's
change
event, which triggers calculation, which triggers the change event,
etc. If
you remove all bound data instances in all of the controls, perhaps
even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to
Manual and
try to replicate the problem. (Calc to Manual, save workbook, close,
open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to
link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using
RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if you
step
through! AND you only get the other errors when you are trying to
save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately
precedes
the
crash. Hopefully you can reliably reproduce the error and work
around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she
started
getting
"Microsoft Office Excel has encountered a problem and needs to
close.
We
are
sorry for the inconvenience." In other words it crashed with no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the file
and
re-run
it
works fine. Re-start, exit without saving and it crashes again
on
re-start. I
also get intermittent "Automation Error" normally when trying
to
save
the
file. The other error I get is "Not enough system resources to
display
completely". I have tried deleting some of the form content and
some
of
the
code and you think you have fixed it, you save it, re-start it
and
it
works
fine. Then you make a change, which could be as simple a typing
a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running
out of
memory
and my college only has 512MB, but I have 2GB. I also found a
note
about
zooming to 100%, and this appeared to work i.e. changing two
sheets
from
80%
to 100%. Then made a simple change (cell update) and I am back
to
where
I
started.

There are only 250 lines of very basic code, but there are a
lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

A couple of observations:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

Try adding the External argument to the Address property. It pinpoints the
range
exactly:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address(External:=True)

You'll see the difference if you debug.print the differences. In the
immediate window:

?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address
?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address(External:=True)
(check the spacing in "DXP Form". My newsreader inserted a linebreak and I
could not see if there were 1 or 2 spaces).

Without the External argument, Excel will update the *active* sheet. So if a
sheet other than "DXP Form" is active during the ControlSource assignment,
Excel will stick the selected value from the control into THAT sheet's cell,
not "DXP Form". I'm suspect that's not the behavior you want, instead that
the value should always go into "DXP Form". The result, depending on how
your controls are setup, can possibly trigger another control's Change
and/or Click events if the 2nd control's ControlSource is referring to the
same range as the 1st control.

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

cmd line? Not sure what you mean.


I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.

I was thinking of exporting everything to a new workbook, but not importing
back. If the workbook is corrupted, you'll have to rebuild the workbook.
But, that's a "worst case", last-resort. No need to focus on that just yet.


--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.


After removing all the controlsource code this is the first line on a
macro
to load a value back to the form:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

I am still testing by slowly adding back in my code so will post another
update good or bad.

Thanks for the continued encouragement, cos I'm about to throw this excel
app in the bin!

--
Trefor


"Tim Zych" wrote:

I know the type of debugging difficulties this can pose. A similar
situation
occurred to me at one time, requiring workbook close/reopens, incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time.
Once
you can do that, it is much easier to localize the problem and fix it.




--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Not fixed! All references to ControlSource and Row source have been
removed
from the forms, but it still crashes on start up, but never from the
VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not
much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart,
then I
have to change data in any of the form fields, then save, then exit,
then
restart and it crashes. Having to go through this loop every time I
wish
to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and
then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it
fixed
the problem, so now I guess I will have to right some code to load
them
with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering
recursion,
where a change in a cell triggers calculation and/or the control's
change
event, which triggers calculation, which triggers the change event,
etc. If
you remove all bound data instances in all of the controls, perhaps
even
disable the events associated with them, you can see if the problem
is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to
Manual and
try to replicate the problem. (Calc to Manual, save workbook, close,
open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to
link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using
RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if
you
step
through! AND you only get the other errors when you are trying
to
save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately
precedes
the
crash. Hopefully you can reliably reproduce the error and work
around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she
started
getting
"Microsoft Office Excel has encountered a problem and needs
to
close.
We
are
sorry for the inconvenience." In other words it crashed with
no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved
it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the
file
and
re-run
it
works fine. Re-start, exit without saving and it crashes
again
on
re-start. I
also get intermittent "Automation Error" normally when
trying
to
save
the
file. The other error I get is "Not enough system resources
to
display
completely". I have tried deleting some of the form content
and
some
of
the
code and you think you have fixed it, you save it, re-start
it
and
it
works
fine. Then you make a change, which could be as simple a
typing
a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running
out of
memory
and my college only has 512MB, but I have 2GB. I also found
a
note
about
zooming to 100%, and this appeared to work i.e. changing two
sheets
from
80%
to 100%. Then made a simple change (cell update) and I am
back
to
where
I
started.

There are only 250 lines of very basic code, but there are a
lot of
userform
details so it is not easy for me to paste here. Any ideas?

--
Trefor

Tim,

For the code below that failed, I added this into the form as the
ControlSource "=DXP_AM" and it worked fine, but then it stopped on another
line, 20 or so lines down, same issue, same fix.

Thanks for the (External:=True) tip, I have not seen that before.

You assumption was correct I always wanted to write to the "DXP Form" sheet
even if it was not the active one. This is why I added
"ThisWorkbook.Sheets("DXP Form")." on the front of the Range parameter
because I could see it was not always getting the data from the correct sheet.

cmd line as in Command Line, sorry its probably a Unix or DOS term, I meant
double clicking the file from an Explorer Window.

I have been using the MultiPage tool on the form and had pages 5 deep. As
part of trying anything, I removed one level and all me errors disappeared. I
have now reactivated all the code including the one I mentioned earlier and
they all now work ok. So far no crashes, I'm feel good at this point, but Im
not shouting to loud just yet.

If this does indeed fix my issue my guess is there is a bug in Excel if
there are two many levels of pages each with a lot of data on.

--
Trefor


"Tim Zych" wrote:

A couple of observations:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

Try adding the External argument to the Address property. It pinpoints the
range
exactly:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address(External:=True)

You'll see the difference if you debug.print the differences. In the
immediate window:

?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address
?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address(External:=True)
(check the spacing in "DXP Form". My newsreader inserted a linebreak and I
could not see if there were 1 or 2 spaces).

Without the External argument, Excel will update the *active* sheet. So if a
sheet other than "DXP Form" is active during the ControlSource assignment,
Excel will stick the selected value from the control into THAT sheet's cell,
not "DXP Form". I'm suspect that's not the behavior you want, instead that
the value should always go into "DXP Form". The result, depending on how
your controls are setup, can possibly trigger another control's Change
and/or Click events if the 2nd control's ControlSource is referring to the
same range as the 1st control.

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

cmd line? Not sure what you mean.


I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.

I was thinking of exporting everything to a new workbook, but not importing
back. If the workbook is corrupted, you'll have to rebuild the workbook.
But, that's a "worst case", last-resort. No need to focus on that just yet.


--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.


After removing all the controlsource code this is the first line on a
macro
to load a value back to the form:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

I am still testing by slowly adding back in my code so will post another
update good or bad.

Thanks for the continued encouragement, cos I'm about to throw this excel
app in the bin!

--
Trefor


"Tim Zych" wrote:

I know the type of debugging difficulties this can pose. A similar
situation
occurred to me at one time, requiring workbook close/reopens, incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time.
Once
you can do that, it is much easier to localize the problem and fix it.




--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Not fixed! All references to ControlSource and Row source have been
removed
from the forms, but it still crashes on start up, but never from the
VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not
much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart,
then I
have to change data in any of the form fields, then save, then exit,
then
restart and it crashes. Having to go through this loop every time I
wish
to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and
then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it
fixed
the problem, so now I guess I will have to right some code to load
them
with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering
recursion,
where a change in a cell triggers calculation and/or the control's
change
event, which triggers calculation, which triggers the change event,
etc. If
you remove all bound data instances in all of the controls, perhaps
even
disable the events associated with them, you can see if the problem
is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to
Manual and
try to replicate the problem. (Calc to Manual, save workbook, close,
open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource to
link
the
userform fields to the cells on the sheet, yes. Is this a problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using
RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash if
you
step
through! AND you only get the other errors when you are trying
to
save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately
precedes
the
crash. Hopefully you can reliably reproduce the error and work
around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and she
started
getting
"Microsoft Office Excel has encountered a problem and needs
to
close.
We
are
sorry for the inconvenience." In other words it crashed with
no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped the
Auto_Open
sub
running by simply making the first line an Exit Sub, saved
it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save the
file
and
re-run
it
works fine. Re-start, exit without saving and it crashes
again
on
re-start. I
also get intermittent "Automation Error" normally when
trying
to
save
the
file. The other error I get is "Not enough system resources
to
display
completely". I have tried deleting some of the form content
and
some
of
the
code and you think you have fixed it, you save it, re-start
it
and
it
works
fine. Then you make a change, which could be as simple a
typing
a
character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running
out of
memory
and my college only has 512MB, but I have 2GB. I also found
a
note
about
zooming to 100%, and this appeared to work i.e. changing two
sheets
from
80%
to 100%. Then made a simple change (cell update) and I am
back
to
where
I
started.

Sounds like you're getting closer to a fix.

my guess is there is a bug in Excel if
there are two many levels of pages each with a lot of data on.

If that is true, I'm curious why the workbook crashes except when executed
from within the VBE?

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

For the code below that failed, I added this into the form as the
ControlSource "=DXP_AM" and it worked fine, but then it stopped on another
line, 20 or so lines down, same issue, same fix.

Thanks for the (External:=True) tip, I have not seen that before.

You assumption was correct I always wanted to write to the "DXP Form"
sheet
even if it was not the active one. This is why I added
"ThisWorkbook.Sheets("DXP Form")." on the front of the Range parameter
because I could see it was not always getting the data from the correct
sheet.

cmd line as in Command Line, sorry its probably a Unix or DOS term, I
meant
double clicking the file from an Explorer Window.

I have been using the MultiPage tool on the form and had pages 5 deep. As
part of trying anything, I removed one level and all me errors
disappeared. I
have now reactivated all the code including the one I mentioned earlier
and
they all now work ok. So far no crashes, I'm feel good at this point, but
I'm
not shouting to loud just yet.

If this does indeed fix my issue my guess is there is a bug in Excel if
there are two many levels of pages each with a lot of data on.

--
Trefor


"Tim Zych" wrote:

A couple of observations:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

Try adding the External argument to the Address property. It pinpoints
the
range
exactly:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address(External:=True)

You'll see the difference if you debug.print the differences. In the
immediate window:

?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address
?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address(External:=True)
(check the spacing in "DXP Form". My newsreader inserted a linebreak and
I
could not see if there were 1 or 2 spaces).

Without the External argument, Excel will update the *active* sheet. So
if a
sheet other than "DXP Form" is active during the ControlSource
assignment,
Excel will stick the selected value from the control into THAT sheet's
cell,
not "DXP Form". I'm suspect that's not the behavior you want, instead
that
the value should always go into "DXP Form". The result, depending on how
your controls are setup, can possibly trigger another control's Change
and/or Click events if the 2nd control's ControlSource is referring to
the
same range as the 1st control.

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

cmd line? Not sure what you mean.


I exported all the modules and forms, then copied all the sheets from
one
workbook to another and then imported everything back, no luck.

I was thinking of exporting everything to a new workbook, but not
importing
back. If the workbook is corrupted, you'll have to rebuild the workbook.
But, that's a "worst case", last-resort. No need to focus on that just
yet.


--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

I exported all the modules and forms, then copied all the sheets from
one
workbook to another and then imported everything back, no luck.


After removing all the controlsource code this is the first line on a
macro
to load a value back to the form:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.

I am still testing by slowly adding back in my code so will post
another
update good or bad.

Thanks for the continued encouragement, cos I'm about to throw this
excel
app in the bin!

--
Trefor


"Tim Zych" wrote:

I know the type of debugging difficulties this can pose. A similar
situation
occurred to me at one time, requiring workbook close/reopens,
incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does
that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time.
Once
you can do that, it is much easier to localize the problem and fix it.




--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Not fixed! All references to ControlSource and Row source have been
removed
from the forms, but it still crashes on start up, but never from the
VBE.

I have rem'd out a fair chunk of the code and it is now stable, but
not
much
is left. To bring back the error I can change various lines of code
i.e
nothing in particular, then I have to save, then I have to restart,
then I
have to change data in any of the form fields, then save, then exit,
then
restart and it crashes. Having to go through this loop every time I
wish
to
test even one change is obviously very time consuming. The process
of
elimination is not logical because one piece of code work one time
and
then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?

--
Trefor


"Trefor" wrote:

Tim,

By removing all of the Forms ControlSource and RowSource values, it
fixed
the problem, so now I guess I will have to right some code to load
them
with
the desired value.

Many thanks.

--
Trefor


"Tim Zych" wrote:

Maybe. The reason I ask is because something might be triggering
recursion,
where a change in a cell triggers calculation and/or the
control's
change
event, which triggers calculation, which triggers the change
event,
etc. If
you remove all bound data instances in all of the controls,
perhaps
even
disable the events associated with them, you can see if the
problem
is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to
Manual and
try to replicate the problem. (Calc to Manual, save workbook,
close,
open,
etc).

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
bound data? If you mean have I used RowSource and ControlSource
to
link
the
userform fields to the cells on the sheet, yes. Is this a
problem?


--
Trefor


"Tim Zych" wrote:

Do you have bound data, say to comboboxes or listboxes, using
RowSource
and/or ControlSource?

--
Tim Zych
SF, CA

"Trefor" wrote in message
...
Tim,

Thanks for your reply, but that's my point it doesn't crash
if
you
step
through! AND you only get the other errors when you are
trying
to
save.

--
Trefor


"Tim Zych" wrote:

Step through the macro and find the point that immediately
precedes
the
crash. Hopefully you can reliably reproduce the error and
work
around
the
bug.


--
HTH

Tim Zych
SF, CA


"Trefor" wrote in message
...
I have been jointly developing a form with a college and
she
started
getting
"Microsoft Office Excel has encountered a problem and
needs
to
close.
We
are
sorry for the inconvenience." In other words it crashed
with
no
errors
or
indication of what the problem might be.

If you disable macro's it comes up fine. I then stopped
the
Auto_Open
sub
running by simply making the first line an Exit Sub,
saved
it,
re-ran
the
Auto_Open sub routine from VBE and it works fine. Save
the
file
and
re-run
it
works fine. Re-start, exit without saving and it crashes
again
on
re-start. I
also get intermittent "Automation Error" normally when
trying
to
save
the
file. The other error I get is "Not enough system
resources
to
display
completely". I have tried deleting some of the form
content
and
some
of
the
code and you think you have fixed it, you save it,
re-start
it
and
it
works
fine. Then you make a change, which could be as simple a
typing
a
character
into a cell, save it, exit re-start it and it crashes
again.

In researching this error I have read comments about
running
out of
memory
and my college only has 512MB, but I have 2GB. I also
found
a
note
about
zooming to 100%, and this appeared to work i.e. changing
two
sheets
from
80%
to 100%. Then made a simple change (cell update) and I am
back
to
where
I
started.