Strange Combobox Change Event / Sizing Behaviour 1

[osx99]

Can anyone replicate this weird behaviour and offer a possible workaround?

I have a combobox which I want to resize back to normal on a change event. The change event fires and the code runs but the combobox doesn't resize when running live. If I step through using a break then resize does work??

1. Excel 2003, create a new combobox (Combobox1) from the View/Toolbars/Control Toolbox menu on Sheet1 of a new workbook
2. Number cells on excel range A1:A5 1 to 5
3. Set Listfillrange of Combobox1 to A1:A5
4. Copy this to the VBA editor, Sheet1

Private Sub ComboBox1_DropButtonClick()
ComboBox1.Width = 264
End Sub

Private Sub ComboBox1_Change()
ComboBox1.Width = 100
End Sub

When testing the Combobox won't resize back to 100 on change. The event fires but no update, try putting a break on Private Sub ComboBox1_Change() then change the combobox and the F8 through code. Go back to excel and the combobox has resized???

 

[combo]

[The DropButtonClick event fires twice:
quote "vba help"]DropButtonClick Event
Occurs whenever the drop-down list appears or disappears.[/quote]




combo

 

[combo]

The DropButtonClick event fires twice:

DropButtonClick Event
Occurs whenever the drop-down list appears or disappears.

combo

 

[osx99]

I didn't know it fires twice, this is really going to simplify some other event handlers I need. Thanks Combo.

 

[combo]

Thanks for the star.
I prefer old form controls on the sheet, MSForms and VB controls sometimes seem to be not well mathed to the sheet. If you add a message to your original code, combobox changes the size:

Private Sub ComboBox1_Change()
ComboBox1.Width = 100
MsgBox "change"
End Sub

combo