This thread discusses a problem I've been having with ActiveX objects in an Excel spreadsheet. It's a mess to read through and ultimately doesn't have a cohesive answer.
The problem is 100% reproduceable:
.Font.Size
parameter but something which cannot be changed after the problem occurs, other than continually increasing the fontsizeThe only seemingly authoritative solution involves a MS patch (it was a "hotfix" several years ago, though, so it doesn't seem practical for full deployment) and registry edits, which is not practical for my use case.
I am looking for a way to either:
There is a lack of authoritative information on this problem online. I am intending to post my work around, however, it is not even close to ideal and I would much prefer a better solution.
UFI ActiveX controls are disabled When you open a document, the Message Bar appears, notifying you about the presence of an ActiveX control. If you click Enable Content on the Message Bar, a dialog box appears, giving you the option to enable or disable the ActiveX control.
If you get a security warning that says ActiveX controls have been disabled, you can choose Enable Content to get this done. Another way that you can enable ActiveX controls in Excel is through your settings section. To do this, click on the File tab. Choose Enable Content in the Security Warning area.
Use the following instructions to enable or disable ActiveX controls in the Trust Center. Click File > Options. Click Trust Center > Trust Center Settings > ActiveX Settings. Click the options you want, and then click OK.
If the shortcut menu contains the command Properties, the control is an ActiveX control, and you are in design mode. If the shortcut menu contains the command Assign Macro, the control is a Form control.
My work around is to programmatically iterate through all OLE objects on the sheet* and write code to the debugger, then include a button basically "resize objects" on the sheet - with instructions on why this problem is occurring.
This method will generate the code to drive that button.
It will not automatically update however - it is a snapshot and should only be used immediately prior to deployment of an app (if end users are going to have the button functionality).
The sequence then becomes:
Private Sub printAllActiveXSizeInformation()
Dim myWS As Worksheet
Dim OLEobj As OLEObject
Dim obName As String
Dim shName As String
'you could easily set a for/each loop for all worksheets
Set myWS = Sheet1
shName = myWS.name
Dim mFile As String
mFile = "C:\Users\you\Desktop\ActiveXInfo.txt"
Open mFile For Output As #1
With myWS
For Each OLEobj In myWS.OLEObjects
obName = OLEobj.name
Print #1, "'" + obName
Print #1, shName + "." + obName + ".Left=" + CStr(OLEobj.Left)
Print #1, shName + "." + obName + ".Width=" + CStr(OLEobj.Width)
Print #1, shName + "." + obName + ".Height=" + CStr(OLEobj.Height)
Print #1, shName + "." + obName + ".Top=" + CStr(OLEobj.Top)
Print #1, "ActiveSheet.Shapes(""" + obName + """).ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft"
Print #1, "ActiveSheet.Shapes(""" + obName + """).ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft"
Next OLEobj
End With
Close #1
Shell "NotePad " + mFile
End Sub
*note: this will not find objects which are grouped, unfortunately, either.
The only 100% reliable workaround is to close and restart Excel (including any invisible instances). Any other solution has some problem.
That's one of the reasons why I avoid controls when possible. See here for an example.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With