Excel VBA ComboBox DropDown Button Size--changed itself

Question

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

Now I notice that the whole dropdown box, when the huge button is pressed, is itself huge, or hugely wide, that is. The text is the same size as the normal boxes. And the row height is fine. But the box is super wide, and it has a huge right-side scroll bar in it. — RomnieEE2, Mar 01 '12 at 14:35
Well it's not pretty, but I think I have a workaround. First I format the box with autosize true. Twice, I guess. As the box got wider and the button got smaller on both passes. (Note, none of the text was as wide as my original box width.) Then format the boxes to my desired widths, with autosize false. — RomnieEE2, Mar 01 '12 at 14:47
In the same vein, in my Workbook_Open method, I actually have to run my format combo boxes routine twice, b/c the listboxes don't populate on the first pass, but do on the 2nd pass. Go figure. Do I curse Excel, vba, or just microsoft in general? — RomnieEE2, Mar 01 '12 at 14:49
See related answers/question here: http://stackoverflow.com/q/1573349/1047635 — matt_black, Jul 03 '12 at 09:33

score 2 · Answer 1 · answered Mar 18 '13 at 20:41

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

score 2 · Accepted Answer · answered Mar 01 '12 at 15:12

2

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.

answered Mar 01 '12 at 15:12

SWa

4,174
21
40

Thanks. So I've tried a quick web search, and didn't find it: but you are saying that there's away to access Forms properties in VBA by unhiding them? I should be able to experiment and get the idea--except how do I get started? To see one Form object, as an example? Thanks again. – RomnieEE2 Mar 01 '12 at 16:19
Tools > Object Browser > Right click in Members > Show hidden Members. The form dropdown boxes are then a collection in the sheet sheetx.dropdowns(x) etc. You don't need to actually view the hidden members, the code will run fine without, just makes things a bit easier – SWa Mar 01 '12 at 17:19

score 1 · Answer 3 · edited Nov 19 '15 at 05:15

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:

Combobox Schrift wird kleiner und kleiner
Combobox Schrift ändert sich selbstständig
Combobox Schriftgrösse automatisch kleiner
Combobox automatische Anpassung Schriftgröße deaktivieren

score 1 · Answer 4 · answered Dec 20 '16 at 15:31

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

score 0 · Answer 5 · answered Apr 11 '17 at 16:48

0

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.

answered Apr 11 '17 at 16:48

KevinP

1

score 0 · Answer 6 · answered May 19 '17 at 16:19

0

I just move the shape to fix

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

answered May 19 '17 at 16:19

Aldo Ulate

1
1

score 0 · Answer 7 · answered Jul 24 '18 at 06:11

0

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.

answered Jul 24 '18 at 06:11

Thomas Hind-Valentine

1

score 0 · Answer 8 · answered Feb 13 '21 at 05:24

I have found this issue also where I also connected remotely. I think it has to do with the difference screen resolution on the remote computer and the one you logging in from.

I found that when I copied the combo boxes and deleted the originals, the new ones didnt have the issue. Its a bit annoying to select them all but at least their properties and linked cells remained and were able to be used.

Excel VBA ComboBox DropDown Button Size--changed itself

8 Answers8

Linked