Excel VBA ComboBox下拉按钮大小——改变了自己

I have a workbook with several comboboxes (and listboxes) and a lot of vba written around them. I've used the same code in the Workbook_Open procedure to format them for weeks, without any major trouble.

我有一个工作簿，里面有几个combobox(和listbox)和很多vba。我在Workbook_Open过程中使用了相同的代码对它们进行了数周的格式化，没有遇到任何大麻烦。

Last night I remoted-desktopped in to my work computer (for the 2nd time ever) to edit some other parts of the code (nothing that touched the box properties at all). At one point after a while, the formatting of all the boxes, list and combo, went crazy. The right side scroll bars on the list boxes got huge, and huge scroll bars appeared at the bottom of them too. And the Dropdown button on the comboboxes got huge too--as wide as the box just about.

昨晚我把桌面移到了我的工作电脑上(这是我第二次)去编辑代码的其他部分(根本没有涉及到box属性)。过了一段时间，所有框、列表和组合的格式都变得疯狂起来。列表框的右侧滚动条变得很大，底部也出现了巨大的滚动条。而且连框上的下拉按钮也变大了——几乎和框一样宽。

I closed and reopened Excel, and all the boxes went back to their former happy state, except for one, which still has a dropdown button as wide as the box. My vba formatting code doesn't help. Rebooting the computer doesn't help. I compared the properties window for two boxes that should be identical (except for name and left position), and nothing is different there.

我关闭并重新打开Excel，所有的盒子都回到了他们以前的快乐状态，除了一个，它仍然有一个和盒子一样宽的下拉按钮。我的vba格式代码没有帮助。重新启动计算机是没有用的。我比较了属性窗口中两个应该相同的框(除了名称和左位置)，这里没有什么不同。

So is there anyway I can tame, reset, or otherwise control this renegade dropdown button? I wish I wasn't even in Excel dealing with this kind of unpredictable behavior, but I'm stuck.

那么，有什么方法可以让我驯服、重置，或者以其他方式控制这个叛逆性的下拉按钮呢?我真希望我在处理这种不可预知的行为方面不是出类拔萃的，但我被困住了。

Here is my formatting vba:

以下是我的格式vba:

    With ThisWorkbook.Sheets(c_stMatrixSheet).OLEObjects(c_stMatrixTypeBox)

        .Width = 120
        .Top = 14
        .Left = 878

        Call FormatComboBox(.Object)

        .Object.AddItem c_stAMatrix
        .Object.AddItem c_stBMatrix
        .Object.AddItem c_stCMatrix

        .Object.Text = c_stAMatrix

    End With

...

…

Private Sub FormatComboBox(bxComboBox As msforms.ComboBox)

    With bxComboBox

        .Clear

        .Height = 19.5
        .Font.Name = c_stDropBoxFont
        .Font.Size = 10
        .AutoSize = False
        .Enabled = True
        .Locked = False

        .Placement = xlFreeFloating

    End With

End Sub

6 个解决方案

#1

You've run into the problem of using ActiveX controls on Worksheets, I've had the same problem and it is intermittent and randomly does it.

您遇到了在工作表上使用ActiveX控件的问题，我遇到了相同的问题，它是间歇性的，并且是随机的。

The only way I've found to truly fix things is to use forms controls. These are much more stable on worksheets although hidden from intellisense unless you choose to show hidden objects. They are also quite flexible and offer a good deal of functionality - unless you need events as they don't fire them.

我发现真正修复问题的唯一方法是使用表单控件。这些在工作表上更稳定，尽管隐藏在智能感知之外，除非您选择显示隐藏的对象。它们也非常灵活，提供了大量的功能——除非您需要事件，因为它们不会触发它们。

#2

I had the same issue. Haven't deployed to users yet but the code below seems to work. Just resetting the size each time the worksheet is selected.

我也有同样的问题。还没有部署到用户，但是下面的代码似乎可以工作。每次选择工作表时，只需重新设置大小。

Private Sub Worksheet_Activate()
     ActiveSheet.Shapes("ComboBoxSelectAccount").Width = 300
     ActiveSheet.Shapes("ComboBoxSelectAccount").Height = 20
End Sub

HTH Rick

HTH里克

#3

I spent a lot of time but no suitable solution in the internet.

我在网上花了很多时间，但是没有合适的解决方法。

I had the problem that on my laptop screen (not on the extended desktop monitor in the docking station!) the font size of a activeX combobox in a worksheet got smaller every time I clicked the dropdown button. Until the dropdown button is inaccessable small.

我遇到了这样的问题:在我的笔记本电脑屏幕上(不是在停靠站的扩展桌面显示器上!)每当我点击下拉按钮时，工作表中的activeX combobox的字体就会变小。直到下拉按钮是不可访问的小。

Manually I could reset the font size by changing the combobox size in the developer mode.

通过在开发人员模式中更改combobox大小，我可以手动重置字体大小。

By VBA I do following which solves Microsofts problem:

通过VBA，我做了以下解决微软的问题:

Private Sub MyComboBox_DropButtonClick()
'MyComboBox.Font.Size = 12 'Has no effect!!!
Dim CbxWidth = 300 As Single 'How big the combobox should be
MyComboBox.Width = CbxWidth + 1 
ComboboxUpdate 'or whatever you want to do
MyComboBox.Width = CbxWidth 
End Sub

Hope it dont disturb if I write some german words to help also people in my native laguage:

希望它不会打扰我，如果我写一些德语单词来帮助我母语的人:

Combobox Schrift wird kleiner und kleiner

和凯鹏华盈在一起
Combobox Schrift ändert sich selbstständig

这是一个很好的例子
Combobox Schriftgrösse automatisch kleiner

组合框Schriftgrosse automatisch kleiner
Combobox automatische Anpassung Schriftgröße deaktivieren

组合框automatische Anpassung Schriftgroße deaktivieren

#4

I had the same issue, no idea why, but if you resize it, then it becomes normal again. So I inserted the followings to resolve:

我也有同样的问题，不知道为什么，但是如果你调整它的大小，它就会恢复正常。因此我插入了以下的解决方案:

Private Sub ComboBox1_LOSTFocus()
Application.ScreenUpdating = False
ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
ActiveSheet.Shapes("ComboBox1").ScaleWidth 1.25, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("ComboBox1").ScaleHeight 1.25, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("ComboBox1").ScaleWidth 0.8, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("ComboBox1").ScaleHeight 0.8, msoFalse, _
    msoScaleFromTopLeft
Application.ScreenUpdating = True
End sub

#5

I did some poking around and found if you have PageBreakPreview ON, it will cause the resizing problem. Go back to Normal View and the problem goes away.

我翻了一下，发现如果你有PageBreakPreview ON，它会导致调整大小的问题。回到正常的视角，问题就解决了。

#6

I just move the shape to fix

我只是移动形状来修正

Private Sub MyComboBox_DropButtonClick()
     ActiveSheet.Shapes("ComboBox1").Top = 1
     ActiveSheet.Shapes("ComboBox1").Top = 2
End Sub

#1