Edit Jan 2019: See from post #4 for solution
Excel 2003. ActiveX controls embedded in worksheet not working, then can’t even insert them
Hi
This Thread is related to this Thread: http://www.excelfox.com/forum/showth...ommand-Buttons
I am trying to get clued up on the two different ways to do control buttons in a worksheet in Excel VBA,
( ActiveX controls and Forms controls 2WorksheetButtonTypes.JPG 2WorksheetButonTypesExcel2003.JPG https://imgur.com/T6GZiSV https://imgur.com/yx2eZaj ).
Mostly I am using XL 2007+, but I also want to do some stuff in XL 2003. I’ve hit a snag which is that I cannot get any ActiveX controls to work in Excel 2003.
I have tried a few separate computers with independently installed 2003 ( installed from separate different Discs )
The problem, and what I have done so far to try and solve it
If I click the button, then nothing happens. If I go in the VB Editor and attempt to run it manually ( F5 or F8 ) , then I get one of the following errors:
_ Wechsel aus dem Entwurfsmodus nicht möglich, da das Steuerelement ‘CommandButton1’ nicht erstellt werden kann ( In English something like: Can not switch from design mode because the CommandButton1 control can not be created )
Can not switch from design mode because the CommandButton1 control can not be created.jpg https://imgur.com/O68mGry https://imgur.com/Yloro1L
Can not switch from design mode because the CommandButton1 control can not be created.JPG
_ Laufzeitfehler ‘57121’: Anwendungs- oder objektdefinierter Fehler ( In English something like: Runtime Error '57121': Application or object-defined error )
Runtime Error 57121 Application or object-defined error.jpg : https://imgur.com/dQxg2zd https://imgur.com/7T4pq8y
Runtime Error 57121 Application or object-defined error.JPG
_ *** Note also .. after I started these experiments I now find that I can not even insert an ActiveX control into a worksheet – I get errors Cannot Insert Object
CannotInsertObject.JPG : https://imgur.com/dSUiTKh
CannotInsertObject.JPG
( The work around to at least get them in is to put them in using Excel 2007+ then save as .xls then close the file and reopen in Excel 2003 )
_._______________
I am aware that there have been problems with broken ActiveX controls:
Apparently, most of the problems go back to an update around December 2014 – the internet is full with reports saying that an update broke-ActiveX-controls. I must have seen over a hundred reports, articles and the such on this problem.
As I understand it, this update , MS14-082 ( 3017349 ) , broke stuff, and this should fix it MS15-022 ( 3025036. ) . I can’t find either of those on my computer.
The year 2014 was a year or so before my “Excel Life” began, so I missed all that fun, and I have never experienced any problems with broken controls in Excel 2007+
I looked further, and the internet is littered with questions about similar problems. It seems a random mix that some got their controls finally to work, some never did, even in Excel 2007+
It seems that hardly anyone that had reported problems with their controls not working in Excel 2003 ever got them to work again. For those that did, they had some very exotic solutions and they got me nowhere. To be quite honest I am not sure if those that reported success may have not been aware of the difference and were confusing 3 different things:
1) Buttons from ActiveX controls ( In a worksheet )
2) Buttons from Forms controls ( Always in a worksheet )
3) Buttons from ActiveX controls embedded in a UserForm
This thread is concerned with problems with 1) Buttons from ActiveX controls ( In a worksheet )
_.___________________________
Here is some notes on what I have tried already:
_Updates that might have broke stuff for excel 2003
Some people have mentioned that removing other updates solved the problem… here a few..
2553154 2726958 2965291 2920813 3054873 974554
I looked ( initially manually ### ) in the usual place for these updates ( Systemsteuerung \ Programme \ Programme und Funktionen \ Installierte Updates (Control Panel \ Programs \ Programs and Features \ Installed Updates ) ) , but I could not find those updates. I expected that I did not have them.
( ### Just to be sure as it is knackers your eyes trying to look through the massive list, here is a way I used for searching for Updates automatically:
http://www.excelfox.com/forum/showth...0586#post10586
( You initially need a text file list of all your computer updates to search through, - Here is a way to get that:
http://www.excelfox.com/forum/showth...0582#post10582 )
It and I did not find the updates on my computers )
_Deleting Temporary File things..
I read a bit about .exd files that needed to be deleted.
I have some appendix notes on that here:
http://www.excelfox.com/forum/showth...0588#post10588
None of that deleting of .exd files helped
So the usual tricks and fixes have not helped me so far.,
Some other things that I have noticed or things that might be relevant …
ActiveX controls appear to be working in Excel 2007 and Excel 2010 on the same computers where I am having the issue with Excel 2003
( Note also,
_ that the ActiveX control in the attached .xls file also works if I open it in XL 2007 or XL 2010
_ Form controls are working OK for me in Excel 2003. )
_ *** Since I first started these experiments, I have not been able to even add any ActiveX controls into a Excel 2003 worksheet. I try to insert an ActiveX control button then I get the error that “the object can not be inserted” when I try.
_ Once I have had an ActiveX control in my worksheet, then I can no longer run any code in the code module for that worksheet, even if I remove the control and all codes to do with it. I always then receive_..
_..first an error such as this:
_.. Can not switch from design mode because the CommandButton1 control can not be created.jpg https://imgur.com/wsF5dsf,
_.. then an error such as this:
_.. Runtime Error 57121 Application or object-defined error.jpg https://imgur.com/sBHCNmG
_.. then , in further attempts I get mostly that last error, or I get the next one occasionally :
_.. RuntimeError 32809 Corrupted bei ActiveX control in Worksheet.JPG https://imgur.com/4ueFqI7
_.. I seem to be able to get ActiveX controls to work if I embed them in a UserForm.
_.. I never myself experienced any problems with ActiveX controls – for one thing I never used them until recently, and for another, my “ Excel VBA Life” started a year or so after the big problems with these things started.
Anyone have any ideas how I can get my ActiveX controls to work in a worksheet in Excel 2003? And any other comments about these known ActiveX problems. For example, it is not clear to me if all the problems a few years back effected only the ActiveX controls embedded in worksheets or whether the problems were also noticed in any other situations, such as in UserForms
Attached are two Excel Files ( Excel 2003 .xls ). ( I also have them at box.net File sharing at the share URL links shown )
This first file is a simple file with a single ActiveX Control Button embedded in the first worksheet. The button does not work for me in any Excel 2003 on any computer. ( It does work in all Excel 2007+ versions that I have: The code simply changes the button caption )
Command button ActiveX control Excel 2003.xls
( https://app.box.com/s/8siiuzvqxajaotsmp7usrvfgd12gqz86 )
This second file was originally the previous File, and I am using three instances of a simple UserForm Class object as a workaround to using an ActiveX control Button: Each Userform instance has a single ActiveX control button which is assigned to a different macro. They work OK. The original ActiveX control ( Button ) still in the first worksheet does not work.
Excel 2003 Command button Active Xcontrol and UserForm Instancetaneous Farts.xls
( https://app.box.com/s/kg4htf9o6386051he7xq8q1fmmdoj20s )
Thanks
Alan
Ref:
http://excelmatters.com/2014/12/10/o...ivex-controls/
https://superuser.com/questions/8917...-in-excel-2003
https://stackoverflow.com/questions/...83587#33383587
http://www.eileenslounge.com/viewtop...=28682#p222044
https://support.microsoft.com/en-us/...solution-after
https://stackoverflow.com/questions/...trols-disabled
https://www.excelforum.com/excel-pro...ml#post4382894
https://www.excelforum.com/excel-pro...ml#post4535120
http://www.jkp-ads.com/Articles/cont...nts=True#25923
http://www.excelfox.com/forum/showth...en-insert-them
https://stackoverflow.com/questions/...50074#54350074
https://eileenslounge.com/viewtopic....290751#p290751
Reply With Quote
Bookmarks