VBA Excel comboBox dropdown is empty after obj.addItem

0
votes

I have an Excel2010 VBA userform that has one comboBox, from which the user should be able to select a currently-open Excel Workbook. The USERFORM_Initialize subroutine contains (among other things) :

    cbWorkbook.Clear
    cbWorkbook.Visible = True
    For Each wb In Workbooks
        cbWorkbook.AddItem wb.name
    Next wb

I have set a breakpoint at this code, and am able to step through it; in the present situation there are four open workbooks, and the "for each" is iterated four times, as appropriate. And I can see that wb.name contains the values that I want.

However, when the form displays and the dropbox arrow is clicked, the "list" is empty. It looks like there is room for one item, and that item is blank. (I believe this is typical of an empty dropdown box.)

Select attributes for the combobox are: Autosize=False; AutoTab=false; Enabled=True; DropButtonStyle=1-fmDropButtonStyleArrow; Height=18; ListRows=8; ListStyle=0; Locked=False; ShowOptionWhen=2; SpecialEffect=2; Style=0; Visible=True. At the time of execution, cbWorkbook.listCount = 4

This is in development, and it did appear to work as expected yesterday, but now seems to never work. Any ideas where I might be going wrong?

EDIT: I found the solution to this: I had inadvertantly duplicated another combo box over the top of cbWorksheet, effectively hiding it. The control I was seeing was empty, while the control I wanted was overlaid. Deletion of the rogue control box solved the issues.

My apologies; this should have been the first thing I sought.

2
vbacombobox
Do you have any error handling? - SWa
No error handling anywhere in this workbook at this time. - Dennis
Can you post the full code for the userform? - SWa
If you have discovered your own solution, you should post it as an answer and then accept it so others can more easily identify it in the future. - Gaffi
Thanks. I have done that now. - Dennis

2 Answers

1
votes

I found the solution to this: I had inadvertantly duplicated another combo box over the top of cbWorksheet, effectively hiding it. The control I was seeing was empty, while the control I wanted was overlaid. Deletion of the rogue control box solved the issues.

My apologies; this should have been the first thing I sought.

0
votes
With ComboBox1
   .AddItem "This"
   .AddItem "Is"
   .AddItem "A"
   .AddItem "Test"
End With

or if you want to fill it with Range data:

ActiveSheet.Shapes("ComboBox1").Select
Selection.ListFillRange = "k1:k10"

PS - submit your file for review; it should be easier to look at!