Improve company productivity with a Business Account.Sign Up


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

Posted on 2015-02-10
Medium Priority
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 37

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 52

Expert Comment

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

Assisted Solution

by:Martin Liss
Martin Liss earned 750 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

LVL 52

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 750 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 31

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 52

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 52

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 31

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 52

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 52

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Though there are a few manual ways to import PST files to Office 365 , third-party PST to Office 365 import tools are preferred over them due to various reasons.  Consequently, many tools or services are available for the same. Here, we pick the to…
This article presents several of my favorite code snippets.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

605 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