Solved

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

Posted on 2015-02-10
14
200 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 46

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

Open in new window

0
 
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.
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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!
0
 
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.
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 46

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 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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
KMS and XenDesktop 7.5 15 38
highlight duplicate entry 16 29
Error in formula not increasing value 2 15
IF ELSE Statement in Excel Macro VBA 16 34
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

910 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

21 Experts available now in Live!

Get 1:1 Help Now