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

On Apr 17, 11:34*am, dransfield wrote:
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

On 17 Apr, 17:06, cht13er wrote:
On Apr 17, 11:34*am, dransfield wrote:

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

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

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

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

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

On Apr 22, 9:33*pm, dransfield wrote:
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