Dr Watson is pushing my head under the poo....

[Chris Adams]

Hi,

I was hoping for a useful suggestion to help me find a Dr Watson
error.

I've a 1M calculator (built on 97 XL). It has:
• 7 pages
• 4 of those pages have about 30 comboboxes + 10 checkboxes (ActiveX)
• and the comboboxes & checkboxes on each page are entered into a
class collection upon that sheet being activated

I've:
• replaced all Activex comboboxes + checkboxes
• tried to narrow down the Dr Watson source by process of elimination
(this gets to the 4 pages, but then I realise the activex's on them
are linked to other pages via named ranges)
• deleted all surplus named ranges (and any with INDIRECT functions)
• resized all pages that were zoomed back to 100%
• set all objects in the code to Nothing at the end of each sub
(can/should I do this with the collection?)
• checked/removed any external workbook links
• cleared all rows and columns around the used ranges on each
worksheet
• looked at disabling Dr Watson, but sadly its not possible

As I see it, my only remaining step is to delete and rebuild each
worksheet. I'm putting that off at this stage because (i) its a
daunting task, and (ii) I'm running out of delivery time.

Based on what I've read about the good Dr in archives, I'm not sure
there is an answer, but if there are any useful suggestions, I'd very
much appreciate them.

Many thanks in advance for your time,
Chris Adams

 

[Jamie Collins]

Chris Adams wrote ...

I was hoping for a useful suggestion to help me find a Dr Watson
error.

I've a 1M calculator (built on 97 XL). It has:
? 7 pages
? 4 of those pages have about 30 comboboxes + 10 checkboxes (ActiveX)
? and the comboboxes & checkboxes on each page are entered into a
class collection upon that sheet being activated

I've:
? replaced all Activex comboboxes + checkboxes
? tried to narrow down the Dr Watson source by process of elimination
(this gets to the 4 pages, but then I realise the activex's on them
are linked to other pages via named ranges)
? deleted all surplus named ranges (and any with INDIRECT functions)
? resized all pages that were zoomed back to 100%
? set all objects in the code to Nothing at the end of each sub
(can/should I do this with the collection?)
? checked/removed any external workbook links
? cleared all rows and columns around the used ranges on each
worksheet
? looked at disabling Dr Watson, but sadly its not possible

As I see it, my only remaining step is to delete and rebuild each
worksheet. I'm putting that off at this stage because (i) its a
daunting task, and (ii) I'm running out of delivery time.

I've experienced something similar. In addition to what you described,
I was dynamically adding controls to my worksheet. The problem arose
when the workbook was saved with these controls in it. After the save,
the workbook was effectively corrupted. I could reopen the workbook
but re-saving it caused GPF. I found no way of recovering the
workbook. Of course, I had hourly backups in Visual Source Safe so
nothing was lost. I ended up changing my approach to the one you
described i.e. rather than adding controls at run-time, I had a fixed
number of controls that I made visible/invisible as required. This was
in Excel 2003, I'm not sure if other versions would be similarly
affected.

Did you create any of your controls dynamically? If you did, I'll bet
that your workbook went corrupt at this point. I hope I'm wrong though
and you manage to recover it. I would be interested in hearing any
more details you have.

If you do not have a backup from before this time then yes, I think
you do have to rebuild the workbook. I'd suggest start by removing the
controls and copying the sheets into another workbook.

Jamie.

--

 

[Chris Adams]

Jamie,

Many thanks for your reply and thoughts.

I did not dynamically add the controls - just embedded them at the
beginning. But apparently like your issue, its only when I added in
the activex controls (when I replicate the workbook) that it falls
over.

Incidentally, I easily replicated pretty much the entire workbook
(apart from some slight formatting and the activex controls) by
downloading the Excel 97 File Recovery Macro, available from:
http://www.microsoft.com/office/ork/appa/appa.htm#top

I'm now working on the new book and trying to find a workaround for
the activex comboboxes. For instance, I don't think form comboboxes
let you hide them easily (eg. move and size with cells), or allow you
to reduce their size/font to fit smaller rows. So, unfortunately I'm
looking at data validations (really kills a calculator's looks...!).
But at least it should do the job.

Many thanks again for sharing your experience and thoughts Jamie, much
appreciated.
Regards,
Chris Adams