如何停止ActiveX对象自动改变办公室的大小？ [英] How to stop ActiveX objects automatically changing size in office?

问题描述

这个线程讨论了我在Excel电子表格中使用ActiveX对象的问题。这个问题是100％可重现的：

1. 使用扩展坞时，在电子表格中使用ActiveX对象打开工作簿


2. 断开机器与扩展坞的连接，触发分辨率更改（还有其他原因，我的是一个对接站，似乎改变的分辨率导致这个）


3. 点击一个ActiveX控件 - 他们立即调整大小，字体改变大小。 fontsize更改不是 .Font.Size 参数的功能，但是在问题发生后无法更改的内容，除了不断增加字体大小

唯一看似权威的解决方案涉及到一个MS补丁（这是几年前的修补程序，但是完全部署似乎不实用）和注册表编辑，这对我的用例是不实际的。

我正在寻找一种方法：

<

防止这种变化发生

找到最好的工作

在线问题缺少权威信息。我打算发布我的工作，但是，它甚至没有接近理想，我更喜欢更好的解决方案。

解决方案

我的工作是以编程方式遍历工作表上的所有OLE对象，并将代码写入调试器，然后在工作表上包含一个基本上调整对象大小的按钮，并指出为什么会出现此问题。

此方法将生成驱动该按钮的代码。

它不会自动更新 - 它是一个快照，并且应该在部署应用程序之前立即使用 （如果最终用户将具有按钮功能）。

序列然后变成：

1. 运行以下方法生成的代码


2. 立即保存工作簿 - 这不


3. 重新打开工作簿，问题是解决

---

  Private Sub printAllActiveXSizeInformation（）
 Dim myWS As Worksheet 
 Dim OLEobj As OLEObject 
 Dim obName As String 
 Dim shName As String 
 
'您可以轻松地为所有工作表设置一个for / each循环
设置myWS = Sheet1 
 
 shName = myWS.name 
 
 Dim mFile As String 
 mFile =C：\Users\\你\Desktop\ActiveXInfo.txt
 
 
打开mFile输出为＃1 
与myWS 
对于每个OLEobj在myWS.OLEObjects 
 obName = OLEobj.name 
 
打印＃1，'+ obName 
打印＃1，shName +。 + obName +.Left =+ CStr（OLEobj.Left）
打印＃1，shName +。 + obName +.Width =+ CStr（OLEobj.Width）
打印＃1，shName +。 + obName +.Height =+ CStr（OLEobj.Height）
打印＃1，shName +。 + obName +.Top =+ CStr（OLEobj.Top）
打印＃1，ActiveSheet.Shapes（+ obName +）.ScaleHeight 1.25，msoFalse，msoScaleFromTopLeft
打印＃1，ActiveSheet.Shapes（+ obName +）.ScaleHeight 0.8，msoFalse，msoScaleFromTopLeft
 
下一页OLEobj 
结束
 
关闭＃1 
 
 ShellNotePad+ mFile 
 
 
 
 End Sub 
  

*注意：不幸的是，这不会找到被分组的对象。

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.

解决方案

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.

这篇关于如何停止ActiveX对象自动改变办公室的大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！