David,
Sounds like you need to include/improve your error handling. Do all
(necessary) routines have the statement;
On Error {one of the options}

Although this will not stop errors, it will allow you to test the
cause/current state and deal with the situation.
As for populating with stored data, wouldn't the Workbook_Open event be
better ?

In VBA, the End statements causes all variables to be cleared. I would
assume this is effectively what happens in your error situation(s).
So basically, you need to deal with the error before it reaches this stage.

NickHK

"David" wrote in message
...
Hi

I have a problem that's been plaguing me for a while now.

I have a fairly complicated Excel (97) Workbook made to
create html pages (it's a template for generating html in
effect). The root of the problem is that list boxes and arrays
that contain data at runtime have to be persisted into a hidden
worksheet when the app is shut down, otherwise it's lost.
During startup, I found I can re-populate the controls and
arrays from the hidden spreadsheet so the app is left in the
same state as it was when shut down. This seems to work
fine using the Workbook_WindowActivate event.

The real problem is when there's an un-handled exception.
All data in the controls and arrays disappear, and once
'Reset' from the Visual Basic Editor, all the data is missing.
In my program, if a person inadvertently saves the document
(generates the html template), it's generated without the
data... heh heh.......erasing my 'work in progress' in the html
file. For the most part I can usually keep it together, but it's
a hassle... and more than once I've generated a template
with data missing.

(...by the way......my code doesn't really generate all that
many exceptions......just a few.........sometimes......but
always at the worst possible moments......)

I can see where a button could be set on one of the sheets
to update the mess, but multiple working sheets mean multiple
buttons ect. Shutting down the app at that point without saving
also is not really an option, because all the other changes made
since the last save are lost........I guess that's normal when it
comes to apps in general, but VBA allows you to continue from
where you left off, and that's a bit dangerous. Even if the app
was forced to completely terminate in such a case I think it
would be better. It would sure be better in the event someone
else needed to use it that wasn't so familiar with the workings.

Is there some 'event' that is generated back to the Workbook
or app so that it knows it's in a 'reset' state??? That way, all the
pages could be updated, such as at startup. I've tested all the
events generated in the Workbook_xxx but to no avail.......it
seems there is no indication whatsoever that the app has
been 'Reset' and the data is actually in quite a bit different state
than it was before 'Reset'.

Maybe it's just a limitation I have to live with? I don't really
want to put error handlers in every function and sub......but..??
Could wrap the whole thing in a VB app, but that's a big step
and the convenience of Excel for such things would be lost.....

Thanks for any help!

David Otte