Forms: Why do they cause Excel to crash?

[dransfield]

I've got a set of forms at the moment, that I can open, use, close,
save file, but when I try to close the Excel file it crashes Excel,
every time.

Any idea how I can stop this?
Is it something to do with how I've closed the forms?, do I need a
clean up routine? Transfer everything to a new file?

Thanks in advance
Mark

 

[cht13er]

I've got a set of forms at the moment, that I can open, use, close,
save file, but when I try to close the Excel file it crashes Excel,
every time.

Any idea how I can stop this?
Is it something to do with how I've closed the forms?, do I need a
clean up routine? Transfer everything to a new file?

Thanks in advance
Mark

Can you post the code you're using?

Chris

 

[dransfield]

Can you post the code you're using?

Chris

My forms tool is made up of 1000+ lines of code.
I've got 3 main forms which run in a chain: There's a button on the
first which opens up the next with this kind of code

Private Sub CommandButton10_Click()
Me.Hide
MacroToLoadandOpenForm2
End Sub


sub MacroToLoadandOpenForm2
..................code to populate all the controls
etc............
Form2.show
end sub

The forms are non-modal.
The thing is at the moment it all runs fine: Opens, closes, flicks
backwards and forwards between forms, closes down, file saves, but it
crashes Excel when I close the file. Its as if something hasn' cleared
or realeased, and Excel gets confused when the file tries to close
while this thing is still running.

Any ideas?
TIA
Mark

 

[dransfield]

Okay, I'm really starting to tear my hair out now - Excel crashes when
I try to close my file.
I've got some forms - they're quite complicated, with 1300+ lines of
code.
However the problem that's presenting itself is quite simple:
I've got a routine called "sub FillForm()" which gets the data off the
worksheet and populates the controls on the form. It works fine - the
whole projects works fine (until you try and close the file!).
If I comment out the whole of "sub FillForm()" then I don't get the
crash.
So I've been going through "sub FillForm()" commenting in and out
bits to see what causes the crash, and it appears that a line as
simple as this...

b24value = ThisWorkbook.Sheets("Lists").Range("b24").Value (its not
a variable type problem)
or
Sheets("Lists").Visible = True
or
MsgBox ThisWorkbook.Sheets("Lists").Range("b24").Value

will cause a crash.
Its as is half way through this macro the code doesn't like referring
to the workbook any more.

Lets be clear though, by 'crash' I mean Excel crashes when I try to
close the file. The whole project itself runs perfectly; it will get
that value from cell b24, populate a control with it, etc, but unless
I edit out those references to the workbook then a crash will happen
later. The macro "FillForm" even refers to the workbook before these
lines without a hitch..........
....................Whoaa There! I think I've just cracked it myself.
As I was typing I thought "What's different about these lines?" and I
realised that they refer to the worksheet called "Lists", so on a
hunch a renamed it "List1", changed all the code accordingly, and it
looks like (fingers crossed) its running without a hitch!
So I reckon Excel was running allowing me to use this special word
"List" but really it messed with its mind somewhere along the way.

I hope that's of some use to someone: "Don't call a sheet "List" (or
probably any other possibly 'reserved' name)"

Dz

 

[dransfield]

I don't know if anyones following this, but my above 'fix' didn't
work.
I wondered if the sheet itself is corrupted, so I exported it and
replaced it with another one (which I named "Liss").
The error goes away for a bit, but then just when I feel smug it comes
back.
ARRRGHGHHHHH!!!
Can anyone help?
Regards,
Mark

 

[dransfield]

Here's the latest: I don't know whether I've completely fixed it but
its certainly better:

The sheet I was reading from (and writing to) is also the
ControlSource for some other controls.
That's the only thing that I can think of that makes this sheet
different from the others.
So I split them onto 2 new sheets: Control sources on New1, data I'm
reading from and writing to with code on New2, leaving the original
sheet ("Liss") redundant (and deleteable.

Seems to work better now.
Mark

 

[Dave Peterson]

You haven't gotten much help with this problem and I think that the reason is
that most people don't have this kind of trouble. (Yeah, that doesn't help,
either.)

But you may want to consider rebuilding your workbook--including all the data,
all the shapes/objects/names/code/userforms/pagesetups (yech!).

You may have considered this and realized that even if you spent hours and hours
doing this (and got it correct), it may not fix the problem.

But if it doesn't start behaving better, you may want to build a mini-version of
your workbook to see if you can get it to play nice.

If it starts acting up, at least you didn't spend too much time on the rebuild.

A few months ago, Jan Karel Pieterse was working on a workbook recreator.
http://www.jkp-ads.com

I searched his site, but didn't find any reference to this utility. (Maybe it's
not quite ready to share with the world???)

If you wanted to volunteer to be a guinea pig, you could contact him via his
site:
http://www.jkp-ads.com/Contact.htm

The worst he could say would be no. He might want to hear your results and
knowing Jan Karel, if it didn't work, he's want to get a copy of your workbook
so that he could improve his program.

====
Jan Karel, if you're reading the newsgroup, "hi!"

 

[jkpieterse]

Hi Dave,

But you may want to consider rebuilding your workbook--including all the data,
all the shapes/objects/names/code/userforms/pagesetups (yech!).

My workbook rebuilder does all that indeed.

I searched his site, but didn't find any reference to this utility.  (Maybe it's
not quite ready to share with the world???)

It isn't quite done yet, but the part that is seems to work just fine.

The worst he could say would be no.  He might want to hear your results and
knowing Jan Karel, if it didn't work, he's want to get a copy of your workbook
so that he could improve his program.

So how did you guess <g>?

Regards,

Jan Karel Pieterse
www.jkp-ads.com

 

[dransfield]

Thanks for your replies guys.
It does actually look like I've solved it by separating Control Source
data and other Form write/read data onto different sheets. (They were
completely different cells).
I actually only had a few simple controls with ControlSource, as I
suspected, and this excercise has confirmed, that ControlSource is the
work of the devil!

However, I was on the verge of rebuilding the tool, and so Jan's
utility would have been great for this as although it probably
wouldn't have solved it, it would have avoided a big timewasting
excercise.
I've stored Jan's url as I'm sure I'll need that oneday.
Cheers,
Mark

 

[Dave Peterson]

That's a good tip.

I've never used it and now I have a reason! <vbg>.

 

[Alan]

Okay, I'm really starting to tear my hair out now -Excelcrashes when
I try to close my file.
I've got some forms - they're quite complicated, with 1300+ lines of
code.
However the problem that's presenting itself is quite simple:
I've got a routine called "sub FillForm()" which gets the data off the
worksheet and populates the controls on the form. It works fine - the
whole projects works fine (until you try and close the file!).
If I comment out the whole of "sub FillForm()" then I don't get thecrash.
So I've been going through "sub FillForm()"  commenting in and out
bits to see what causes thecrash, and it appears that a line as
simple as this...

b24value = ThisWorkbook.Sheets("Lists").Range("b24").Value   (its not
a variable type problem)
or
Sheets("Lists").Visible = True
or
MsgBox ThisWorkbook.Sheets("Lists").Range("b24").Value

will cause acrash.
Its as is half way through this macro the code doesn't like referring
to the workbook any more.

Lets be clear though, by 'crash' I meanExcelcrashes when I try to
close the file. The whole project itself runs perfectly; it will get
that value from cell b24, populate a control with it, etc, but unless
I edit out those references to the workbook then acrashwill happen
later. The macro "FillForm" even refers to the workbook before these
lines without a hitch..........
...................Whoaa There! I think I've just cracked it myself.
As I was typing I thought "What's different about these lines?" and I
realised that they refer to the worksheet called "Lists", so on a
hunch a renamed it "List1", changed all the code accordingly, and it
looks like (fingers crossed) its running without a hitch!
So I reckonExcelwas running allowing me to use this special word
"List" but really it messed with its mind somewhere along the way.

I hope that's of some use to someone: "Don't call a sheet "List"  (or
probably any other possibly 'reserved' name)"

Dz

Hi,

Perhaps the file is damaged or something, I think you may try Advanced
Excel Repair at http://www.datanumen.com/aer/ This tool is rather
useful in salvaging damaged Excel xls files.

Alan