kmila
asked on
Loop through and save properties of all activex controls on Excel sheet
Hi, I have a corrupted Excel sheet that I'd like to recreate, however there are a lot of activeX controls on it (I'm learning to avoid placig them directly on a sheet the hard way).
To save a lot of hassle of doing it manually I'm looking for a way to programatically:
1. Loop through all ActiveX controls on a sheet and copy their properties into a matrix on another sheet. The controls I have are combo boxes, textboxes, labels, option buttons and checkboxes.
So not all properties would apply to all controls but in general I'd need the name and visible state of controls plus the following for:
a) Labels: width, heigh, top, left, backcolor, bordercolor, font, forecolor, caption
b) textboxes: width, heigh, top, left, backcolor, bordercolor, font, forecolor, linkedcell
c) comboboxes: all of the above + Listfillrange + Listwidth
d) optionboxes & checkboxes: width, heigh, top, left, backcolor, bordercolor, font, forecolor, linkedcell, caption
2. Delete all ActiveX controls from the sheet while leaving all other form controls.
2. Recreate all ActiveX controls from data stored previously by procedure in point 1.
I'll be greatfull for any hints as to whether my approach is reasonable and how to do it though VBA.
To save a lot of hassle of doing it manually I'm looking for a way to programatically:
1. Loop through all ActiveX controls on a sheet and copy their properties into a matrix on another sheet. The controls I have are combo boxes, textboxes, labels, option buttons and checkboxes.
So not all properties would apply to all controls but in general I'd need the name and visible state of controls plus the following for:
a) Labels: width, heigh, top, left, backcolor, bordercolor, font, forecolor, caption
b) textboxes: width, heigh, top, left, backcolor, bordercolor, font, forecolor, linkedcell
c) comboboxes: all of the above + Listfillrange + Listwidth
d) optionboxes & checkboxes: width, heigh, top, left, backcolor, bordercolor, font, forecolor, linkedcell, caption
2. Delete all ActiveX controls from the sheet while leaving all other form controls.
2. Recreate all ActiveX controls from data stored previously by procedure in point 1.
I'll be greatfull for any hints as to whether my approach is reasonable and how to do it though VBA.
This should be possible but I've got to ask why you want to recreate the ActiveX controls if they are causing problems?
Have they (or their font) changed size? If so then take a look at this article.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One of the problems with what I assume you are going to do which is change the controls to Form controls, is that the font will be very small and you can't change that. You could instead adapt my magical floating ActiveX control article to fit your purposes and you won't have a problem with the ActiveX controls.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a question before you go onto a major reconstitution. You mention:
Can we know why ? and since when as there is a know MS update that renders activex controls useless and there is a fix for that. So maybe before going onto a rampage you want to answer this question first. Is it since December 9 of 2014 after this MS was installed on the pc ?
Here is a link that will describe the problem and a fix if this is your issue.
http://support.microsoft.com/kb/3025036
gowflow
Hi, I have a corrupted Excel sheet that I'd like to recreate,
Can we know why ? and since when as there is a know MS update that renders activex controls useless and there is a fix for that. So maybe before going onto a rampage you want to answer this question first. Is it since December 9 of 2014 after this MS was installed on the pc ?
Here is a link that will describe the problem and a fix if this is your issue.
http://support.microsoft.com/kb/3025036
gowflow
ASKER
Hi, the background to my story is here. The December 9 update has been an issue but it was resolved through deleting .exd files. However I've been hit by some other problem two weeks ago. No solution that's out on the internet works and the only way forward seemed to be recreating the whole sheet. I've tested that already with a couple of controls (creating a new sheet, creating a couple of controls and pasting code behind the control from the corrupted sheet to the control on the new sheet) and it worked on my computer so I was optimistic.
However the funny thing is that when I copied this newly created on my computer sheet to a different computer the names of all controls were reset (back to textbox1, commanbutton1) - so in fact my last resort failed because my code relies on named controls and sheets...
I don't know what MS has changed with couple of last updates but it crippled my application completely. It's been two weeks now and I'm out of options ...
However the funny thing is that when I copied this newly created on my computer sheet to a different computer the names of all controls were reset (back to textbox1, commanbutton1) - so in fact my last resort failed because my code relies on named controls and sheets...
I don't know what MS has changed with couple of last updates but it crippled my application completely. It's been two weeks now and I'm out of options ...
the names of all controls were reset (back to textbox1, commanbutton1) - so in fact my last resort failed because my code relies on named controls and sheets...Right-click on one of the ActiveX controls, choose Properties and change the name!
Or, less desirably, in the code, select one of the control names, do Ctrl+h and change the name of the control everywhere in the code.
Could you simply post the crippled workbook and I will fix it for you ? Instead of going by assumptions and suppositions ?
gowflow
gowflow
ASKER
That gets us to a dropbox with a bunch of items, but none called crippled workbook.
ASKER
I was busy past several days trying to make up for what this Ms update has caused to my work schedule on this application.
Ultimately I was able to solve the problem by installing fresh win7 on my second pc together with office 2010 and applying all available updates and then the hotfixes...however problem still persists on the system and pc I was using originally. I can't say reinstallation is a solution I was looking for but it worked.
Thanks anyway to all of you for trying to help me out.
Ultimately I was able to solve the problem by installing fresh win7 on my second pc together with office 2010 and applying all available updates and then the hotfixes...however problem still persists on the system and pc I was using originally. I can't say reinstallation is a solution I was looking for but it worked.
Thanks anyway to all of you for trying to help me out.
You're welcome.
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014