Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to stop ActiveX objects automatically changing size in office?

Tags:

excel

vba

activex

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:

  1. Open workbook with ActiveX objects in spreadsheet while using a docking station
  2. Disconnect machine from docking station, triggering a resolution change (there are other causes too, mine is with a docking station, it seems changing resolution causes this)
  3. Click an ActiveX control - they immediately resize and the font changes size. The fontsize change is NOT a function of the .Font.Size parameter but something which cannot be changed after the problem occurs, other than continually increasing the fontsize

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

  1. Prevent this change from occuring
  2. Find the best work around

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.

like image 478
enderland Avatar asked Oct 15 '13 16:10

enderland


People also ask

How do I turn off ActiveX controls in Word?

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.

How do I fix ActiveX in Excel?

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.

How do I turn off ActiveX controls?

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.

What is the difference between form control and ActiveX control in Excel?

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.


2 Answers

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:

  1. Run code generated with following method
  2. Save workbook immediately - this does NOT prevent the font changes from continuing to occur
  3. Reopen workbook and problem is "solved"

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.

like image 122
enderland Avatar answered Oct 27 '22 04:10

enderland


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.

like image 40
stenci Avatar answered Oct 27 '22 03:10

stenci