VBA Excel comboBox dropdown is empty after obj.addItem

Question

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.

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
– Gaffi, Commented Mar 9, 2012 at 14:51

Dennis · Accepted Answer · 2012-03-09 23:26:04Z

1

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.

answered Mar 9, 2012 at 23:26

Dennis

1,2842 gold badges21 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew · Accepted Answer · 2012-03-08 17:03:46Z

0

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!

answered Mar 8, 2012 at 17:03

Andrew

7,82813 gold badges69 silver badges121 bronze badges

1 Comment

Dennis Over a year ago

Sorry... how do I submit it for review?

Collectives™ on Stack Overflow

VBA Excel comboBox dropdown is empty after obj.addItem

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related