Active x controls go transparent... but still work!

[Oliver Dewar]

Hi All.

I have an excel app that uses several active x controls. The system can work for days without issue... even weeks. Then suddenly, a macro that's been run thousands of times runs... and the active x controls become rectangles with a red x in the corner.

Its like they simply didn't render correctly, because you can still click on them and they work just fine!

I saw another thread that's similar, but unresolved:

http://www.mrexcel.com/forum/showthread.php?t=523644

I'm not worried about stopping this happening (unless someone already knows the solution) because it seems to be an excel bug... I'd like to know if anyone's found a way to 'refresh' the active x rendering, so I can include a form button to refresh, and therefore continue on without having to close and reopen the workbook as we do now.

Cheeeeeers all!

Excel 2007 - Windows 7.

 

[diddi]

i have had this happen with non MS activex controls. i found that it was more likely to occur if i copied and pasted the control, rather than draw a new one from the toolbox. also had them not display at all but still function when operated as if they where visible (?) try drawing a new control from the toolbox.

 

[Oliver Dewar]

OK I'll give it a crack.

Did this solve your problem entirely?

 

[diddi]

its an odd problem, but as i said it was only with nonMS ocx's specifically CodeJock Suite v13.0 every now and then it turns up and i find that by deleting and drawing a fresh one that it generally works fine afterwards. could be worth check your draw buffer if you have a lot of controls on the form

 

[Oliver Dewar]

OK... please explain about the draw buffer... never heard about that before.

Cheers mate.

 

[diddi]

when you add a new userform, there is a property called .DrawBuffer. the default value is 32000 (bytes), but it can be changed. it is the amount of memory excel allocates to render the form. if it is very complex, then increasing memory can help out.

 

[Oliver Dewar]

Here's my solution.

Remove all active x controls as they are ultimately unstable.

Replace them with buttons created by shapes with 3d bevels, and change the bevel type at the start of the macro, before restoring at the end - to get the button press effect.

Use shape textboxes edited by VBA instead of active x labels.

Works like a charm... and no more dissapearing controls!

 

[diddi]

gee thats a lot of effort...

 

[Oliver Dewar]

Yeah... but my project is a main app for the company... so worth it. Not worth it for something trivial.