On-Worksheet ActiveX Controls - is there a bug in the Excel engine??

[spilly39]

Hi

Can any MVP explain the intermittent errors I & others get when Excel won't
complete the Workbook_Open event, with the message

"Can't exit design mode because Control <control type (or Name?)> can not
be created"

My app has some ActiveX controls placed on a few worksheets as a friendly
way of invoking macros and/or Forms. e.g. my Buttons sheet (hence my UG
name) has several option buttons and a nice big Go button.

I get the error (only sometimes - maddeningly) when WBOpen activates the
Buttons sheet at the end.
It scared the pants off me that I had a corrupt workbook, but it seems that
all is actually well if you switch worksheets to "Buttons" manually and
carry on as normal. But that's no way to write an app.

I've seen complaints about this since Excel 97 & 2K, mainly from less
experienced users, but no authoritative explanation of what's going on.

I'm GUESSING from hints in what I've read, that it may be something to do
with compiling;
that plain vanilla code can execute before ALL the references in every part
of the code have been fully resolved - or something vaguely like that.

Does anyone know what happens behind the scenes when you load a workbook ?
e.g. Are there two paths for the VB engine code depending on whether or the
engine discovers the code needs compiling because the developer hadn't done
so before save on close?

Could it be that my probs will go away by always compiling before save?
Could I rely on that?
Will it stand up to users practices (who won't change the VB code, only the
data)?
It just feels flakey at present, so I'm worried.

Oh yes! One other thing. I use CodeCleaner (indeed I even wrote my own with
a bit of help from Chip Pearson a few years ago), so it isn't an accumulated
cr*p issue.


I planned to post this to the Userform group (where ActiveX controls are
normal), but that UG seems moribund - only one post this year

And I want to start another more specific Userform thread soon too. This
seems the best place unless you say otherwise.


spilly

 

[spilly39]

Sorry to repost, but here is a well written sample of prev problems dating
from May 2007, so I'm not treading new ground

http://www.excelforum.com/excel-pro...rashes-due-to-incomplete-vba-compilation.html

spilly

 

[GS]

spilly39 used his keyboard to write :

Hi

Can any MVP explain the intermittent errors I & others get when Excel won't
complete the Workbook_Open event, with the message

"Can't exit design mode because Control <control type (or Name?)> can not
be created"

My app has some ActiveX controls placed on a few worksheets as a friendly way
of invoking macros and/or Forms. e.g. my Buttons sheet (hence my UG name) has
several option buttons and a nice big Go button.

I get the error (only sometimes - maddeningly) when WBOpen activates the
Buttons sheet at the end.
It scared the pants off me that I had a corrupt workbook, but it seems that
all is actually well if you switch worksheets to "Buttons" manually and carry
on as normal. But that's no way to write an app.

I've seen complaints about this since Excel 97 & 2K, mainly from less
experienced users, but no authoritative explanation of what's going on.

I'm GUESSING from hints in what I've read, that it may be something to do
with compiling;
that plain vanilla code can execute before ALL the references in every part
of the code have been fully resolved - or something vaguely like that.

Does anyone know what happens behind the scenes when you load a workbook ?
e.g. Are there two paths for the VB engine code depending on whether or the
engine discovers the code needs compiling because the developer hadn't done
so before save on close?

Could it be that my probs will go away by always compiling before save?
Could I rely on that?
Will it stand up to users practices (who won't change the VB code, only the
data)?
It just feels flakey at present, so I'm worried.

Oh yes! One other thing. I use CodeCleaner (indeed I even wrote my own with a
bit of help from Chip Pearson a few years ago), so it isn't an accumulated
cr*p issue.


I planned to post this to the Userform group (where ActiveX controls are
normal), but that UG seems moribund - only one post this year

And I want to start another more specific Userform thread soon too. This
seems the best place unless you say otherwise.


spilly

Try using the Autpen and Auto_Close subs in a standard module
instead of putting code behind ThisWorkbook.

I used to get similar problem before I abonded using ThisWorkbook, but
haven't experience any since switching.

 

[GS]

After serious thinking spilly39 wrote :

My app has some ActiveX controls placed on a few worksheets as a friendly way
of invoking macros and/or Forms. e.g. my Buttons sheet (hence my UG name) has
several option buttons and a nice big Go button.

Just curious, Graham.., why would you not create a toolbar for your app
instead of putting controls on worksheets?

 

[spilly39]

The main reason is to improve the user interface
I have a SpinButton & a CommandButton control on a particular worksheet (not
my Buttons sheet) which reconfigures the presentation of certain columns
dynamically. The cmdButton is the "DoIt" button; the spinner sets the
options...
the user spins up/down through various options which change the "DoIt"
caption to show what will happen when they click DoIt.
It's wordy to describe, but very easy to see and use.
I find the Toolbar interface more clunky (& restrictive on size of control
in a custom toolbar), but it is an option I'm considering if all else fails.

And anyway, my question still stands:
-Is there a bug in Excel around this issue?
-What is going on?
If we understand we can produce better products with less pain

spilly

 

[GS]

spilly39 presented the following explanation :

And anyway, my question still stands:
-Is there a bug in Excel around this issue?
-What is going on?
If we understand we can produce better products with less pain

As I stated in my previous reply, I had similar problems. In an effort
to resolve it I discovered there's a condition refered to as 'workbook
corruption' which I initially thought was similar to 'file corruption',
but eventually discovered I was wrong about that association. Fact is,
a corrupt workbook can have many different symptoms; the _Open event
being one place where those symptoms can manifest.

Since you state that you use Rob Bovey's CodeCleaner, you might find it
interesting that Rob is who educated me on the subject of workbook
corruption, and recommended I use the Autpen/Auto_Close method in
place of putting code in ThisWorkbook. To complete the transition away
from that class Rob also educated me on the concept of handling
Application events via a class module. This means I have complete
substitution for ThisWorkbook in all my projects.

Also, according to Rob the CodeCleaner utility won't fix workbook
corruption. The remedy he suggested to me was to export all the code
components with CodeCleaner and import them into a new workbook, and
close/delete the old file before saving the new one with the same name.
Not sure why the latter was necessary but I've never had Rob steer me
wrong in the 7 years I've known him.

 

[spilly39]

Hi Garry
THAT is a well argued reply, for which thank you
I will consider it well

I've considered the wholesale export/import procedure also
Nice to get confirmation this is what you need to fix a case properly

- bit busy right now
spilly
- and I see you've posted on my other thread too (pleased grin)