Excel VBA ComboBox DropDown Button Size--changed i

问题:

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.

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.

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:

    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

回答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.

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

回答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.

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:

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:

1. Combobox Schrift wird kleiner und kleiner

2. Combobox Schrift ändert sich selbstständig

3. Combobox Schriftgrösse automatisch kleiner

4. Combobox automatische Anpassung Schriftgröß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.

回答6:

I just move the shape to fix

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

回答7:

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.