'Excel VBA ComboBox DropDown Button Size--changed itself
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
Solution 1:[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.
Solution 2:[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
Solution 3:[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:
Combobox Schrift wird kleiner und kleiner
Combobox Schrift ändert sich selbstständig
Combobox Schriftgrösse automatisch kleiner
Combobox automatische Anpassung Schriftgröße deaktivieren
Solution 4:[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
Solution 5:[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.
Solution 6:[6]
I just move the shape to fix
Private Sub MyComboBox_DropButtonClick()
ActiveSheet.Shapes("ComboBox1").Top = 1
ActiveSheet.Shapes("ComboBox1").Top = 2
End Sub
Solution 7:[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.
Solution 8:[8]
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.
Solution 9:[9]
The fix for this issue is so stupid, you would not believe it... Set the zoom to a divizor of 10 (80% 90%). This actually fixes the issue. I found that out by mistake, and had to force adjust zoom percentages when a user tries to adjust tehm.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow