Excel VBA ComboBox 下拉按钮大小——改变了自己
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9518002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA ComboBox DropDown Button Size--changed itself
提问by RomnieEE2
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.
我有一个工作簿，里面有几个组合框（和列表框）和很多围绕它们编写的 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.
昨晚我远程桌面到我的工作计算机（有史以来第二次）来编辑代码的其他一些部分（根本没有触及框属性）。一段时间后，所有框、列表和组合的格式都变得疯狂。列表框右侧的滚动条变大了，底部也出现了巨大的滚动条。组合框上的下拉按钮也变得很大——和框一样宽。
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.
那么无论如何我可以驯服、重置或以其他方式控制这个叛逆的下拉按钮？我希望我什至不在 Excel 中处理这种不可预测的行为，但我被卡住了。
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
回答by SWa
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.
我发现真正解决问题的唯一方法是使用表单控件。这些在工作表上更加稳定，尽管在智能感知中隐藏，除非您选择显示隐藏的对象。它们也非常灵活，并提供了大量功能 - 除非您需要事件，因为它们不会触发它们。
回答by Rick Methe
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瑞克
回答by S1000RR
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 组合框的字体大小都会变小。直到下拉按钮不可访问小。
Manually I could reset the font size by changing the combobox size in the developer mode.
我可以通过在开发人员模式下更改组合框大小来手动重置字体大小。
By VBA I do following which solves Microsofts problem:
通过 VBA，我执行以下操作来解决 Microsoft 的问题：
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
Combobox automatische Anpassung Schriftgr??e deaktivieren
Combobox Schrift wird kleiner 和 kleiner
组合框 Schrift ?ndert sich selbstst?ndig
组合框 Schriftgr?sse automatisch kleiner
Combobox automatische Anpassung Schriftgr??e deaktivieren
回答by user7321712
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
回答by KevinP
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，它会导致调整大小的问题。返回普通视图，问题就消失了。
回答by Aldo Ulate
I just move the shape to fix
我只是移动形状来修复
Private Sub MyComboBox_DropButtonClick()
ActiveSheet.Shapes("ComboBox1").Top = 1
ActiveSheet.Shapes("ComboBox1").Top = 2
End Sub
回答by Thomas Hind-Valentine
With listboxes, to prevent them from resizing when you change font or re-open the file, go into the listbox properties and change the "Integral Height" to false.
对于列表框，为了防止它们在更改字体或重新打开文件时调整大小，请进入列表框属性并将“整体高度”更改为 false。
