Loop through and save properties of all activex controls on Excel sheet

Posted on 2015-02-10
Last Modified: 2016-02-12
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.
Question by:kmila
  • 7
  • 3
  • 2
  • +2
LVL 33

Expert Comment

ID: 40601829
This should be possible but I've got to ask why you want to recreate the ActiveX controls if they are causing problems?
LVL 46

Expert Comment

by:Martin Liss
ID: 40601865
Have they (or their font) changed size? If so then take a look at this article.
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40601874
This will get you started on 1.

Sub x()
Dim ctl As OLEObject

On Error Resume Next
For Each ctl In ActiveSheet.OLEObjects
    Debug.Print ctl.Width
End Sub

Open in new window

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 46

Expert Comment

by:Martin Liss
ID: 40601894
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.
LVL 11

Accepted Solution

jkpieterse earned 250 total points
ID: 40602450
You could use code similar to:

Sub ListProperties(ByVal cControl As Object, oSh As Worksheet, lRow As Long)
    'Avoid errors on properties that control does not have
    On Error Resume Next
    oSh.Cells(lRow, 2).Value = cControl.Name
    oSh.Cells(lRow, 3).Value = cControl.Name    'Remember name
    oSh.Cells(lRow, 4).Value = TypeName(cControl)
    oSh.Cells(lRow, 5).Value = cControl.Accelerator
    oSh.Cells(lRow, 6).Value = cControl.ActiveControl
    oSh.Cells(lRow, 7).Value = cControl.Alignment
    oSh.Cells(lRow, 8).Value = cControl.AutoSize
    oSh.Cells(lRow, 9).Value = cControl.BackColor
    oSh.Cells(lRow, 10).Value = cControl.BackStyle
    oSh.Cells(lRow, 11).Value = cControl.BorderColor
    oSh.Cells(lRow, 12).Value = cControl.BorderStyle
    oSh.Cells(lRow, 13).Value = cControl.BoundColumn
    oSh.Cells(lRow, 14).Value = cControl.BoundValue
    oSh.Cells(lRow, 15).Value = cControl.Cancel
    oSh.Cells(lRow, 16).Value = cControl.CanPaste
    oSh.Cells(lRow, 17).Value = cControl.CanRedo
    oSh.Cells(lRow, 18).Value = cControl.CanUndo
    oSh.Cells(lRow, 19).Value = cControl.Caption
    oSh.Cells(lRow, 20).Value = cControl.Column
    oSh.Cells(lRow, 21).Value = cControl.ColumnCount
    oSh.Cells(lRow, 22).Value = cControl.ColumnHeads
    oSh.Cells(lRow, 23).Value = cControl.ColumnWidths
    oSh.Cells(lRow, 24).Value = cControl.ControlSource
    oSh.Cells(lRow, 25).Value = cControl.ControlTipText
    oSh.Cells(lRow, 26).Value = cControl.Cycle
    oSh.Cells(lRow, 27).Value = cControl.Default
    oSh.Cells(lRow, 28).Value = cControl.DrawBuffer
    oSh.Cells(lRow, 29).Value = cControl.Enabled
    oSh.Cells(lRow, 30).Value = cControl.Font
    oSh.Cells(lRow, 31).Value = cControl.Font
    oSh.Cells(lRow, 32).Value = cControl.ForeColor
    oSh.Cells(lRow, 33).Value = cControl.GroupName
    oSh.Cells(lRow, 34).Value = cControl.Height
    oSh.Cells(lRow, 35).Value = cControl.HelpContextID
    oSh.Cells(lRow, 36).Value = cControl.IMEMode
    oSh.Cells(lRow, 37).Value = cControl.InsideHeight
    oSh.Cells(lRow, 38).Value = cControl.InsideWidth
    oSh.Cells(lRow, 39).Value = cControl.IntegralHeight
    oSh.Cells(lRow, 40).Value = cControl.KeepScrollBarsVisible
    oSh.Cells(lRow, 41).Value = cControl.LayoutEffect
    oSh.Cells(lRow, 42).Value = cControl.Left
    oSh.Cells(lRow, 43).Value = cControl.List
    oSh.Cells(lRow, 44).Value = cControl.ListCount
    oSh.Cells(lRow, 45).Value = cControl.ListIndex
    oSh.Cells(lRow, 46).Value = cControl.ListStyle
    oSh.Cells(lRow, 47).Value = cControl.Locked
    oSh.Cells(lRow, 48).Value = cControl.MatchEntry
    oSh.Cells(lRow, 49).Value = cControl.MouseIcon
    oSh.Cells(lRow, 50).Value = cControl.MousePointer
    oSh.Cells(lRow, 51).Value = cControl.MultiSelect
    oSh.Cells(lRow, 52).Value = cControl.Object
    oSh.Cells(lRow, 53).Value = cControl.OldHeight
    oSh.Cells(lRow, 54).Value = cControl.OldLeft
    oSh.Cells(lRow, 55).Value = cControl.OldWidth
    oSh.Cells(lRow, 56).Value = cControl.Parent
    oSh.Cells(lRow, 57).Value = cControl.Picture
    oSh.Cells(lRow, 58).Value = cControl.PictureAlignment
    oSh.Cells(lRow, 59).Value = cControl.PicturePosition
    oSh.Cells(lRow, 60).Value = cControl.PictureSizeMode
    oSh.Cells(lRow, 61).Value = cControl.PictureTiling
    oSh.Cells(lRow, 62).Value = cControl.RowSource
    oSh.Cells(lRow, 63).Value = cControl.ScrollBars
    oSh.Cells(lRow, 64).Value = cControl.ScrollHeight
    oSh.Cells(lRow, 65).Value = cControl.ScrollLeft
    oSh.Cells(lRow, 66).Value = cControl.ScrollTop
    oSh.Cells(lRow, 67).Value = cControl.ScrollWidth
    oSh.Cells(lRow, 68).Value = cControl.Selected
    oSh.Cells(lRow, 69).Value = cControl.SpecialEffect
    oSh.Cells(lRow, 70).Value = cControl.TabIndex
    oSh.Cells(lRow, 71).Value = cControl.TabStop
    oSh.Cells(lRow, 72).Value = cControl.Tag
    oSh.Cells(lRow, 73).Value = cControl.TakeFocusOnClick
    oSh.Cells(lRow, 74).Value = cControl.Text
    oSh.Cells(lRow, 75).Value = cControl.TextAlign
    oSh.Cells(lRow, 76).Value = cControl.TextColumn
    oSh.Cells(lRow, 77).Value = cControl.Title
    oSh.Cells(lRow, 78).Value = cControl.Top
    oSh.Cells(lRow, 79).Value = cControl.TopIndex
    oSh.Cells(lRow, 80).Value = cControl.TripleState
    oSh.Cells(lRow, 81).Value = cControl.Value
    oSh.Cells(lRow, 82).Value = cControl.VerticalScrollBarSide
    oSh.Cells(lRow, 83).Value = cControl.Visible
    oSh.Cells(lRow, 84).Value = cControl.Width
End Sub

Open in new window

LVL 29

Expert Comment

ID: 40602652
Just a question before you go onto a major reconstitution. You mention:

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.


Author Comment

ID: 40609058
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 ...
LVL 46

Expert Comment

by:Martin Liss
ID: 40609076
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!
LVL 46

Expert Comment

by:Martin Liss
ID: 40609082
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.
LVL 29

Expert Comment

ID: 40609486
Could you simply post the crippled workbook and I will fix it for you ? Instead of going by assumptions and suppositions ?

Author Comment

ID: 40611303
Hi, here's the link to this: crippled workbook

I'd be greatful for any assistance.
LVL 46

Expert Comment

by:Martin Liss
ID: 40611308
That gets us to a dropbox with a bunch of items, but none called crippled workbook.

Author Comment

ID: 40655004
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.
LVL 46

Expert Comment

by:Martin Liss
ID: 40655079
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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question