Solved

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

Posted on 2015-02-10
14
193 Views
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.
0
Comment
Question by:kmila
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 33

Expert Comment

by:Norie
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?
0
 
LVL 45

Expert Comment

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

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
Next
End Sub

Open in new window

0
 
LVL 45

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.
0
 
LVL 11

Accepted Solution

by:
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

0
 
LVL 29

Expert Comment

by:gowflow
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.
http://support.microsoft.com/kb/3025036

gowflow
0
 

Author Comment

by:kmila
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 ...
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

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!
0
 
LVL 45

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.
0
 
LVL 29

Expert Comment

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

Author Comment

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

I'd be greatful for any assistance.
0
 
LVL 45

Expert Comment

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

Author Comment

by:kmila
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.
0
 
LVL 45

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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now