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