0

I can not figure out how to properly work with a combobox in VBA. First of all I had tried using the activeX form control but it ended up not working properly (resizing by itself or disappearing all together) Which seems to be a common problem or bug in excel. Something to do with viewing at different resolutions or something like that.

Anyway so here I am. I'm trying to use the form control combo box. How do I change the name from Combo Box 1 to something more appropriate? How do I fill the combo box through VBA? On opening the workbook? How to properly use the combo box? I've been trying different things for a few hours now and I just can't get it to work. I'm about the thrown the towel

Edit: Just a note but you probably wont find any article on this site about form controls that I haven't tried yet.. Also I will add some code in the meanwhile that I have already tried but I posted this already so you can have a though.

Private Sub Workbook_Open()
   Sheet10.DropDowns.Add(280, 70, 200, 20)
end sub

The above adds a combo box to my sheet10. What now? How do I fill it? How do I name it? I don't have a clue

Private Sub Workbook_Open()
   Sheet10.DropDowns.Add(280, 70, 200, 20).Name = cmbTest

   cmbTest.AddItem "test"
   'Tried many different things here to add an item, this is just 1 of the many ways I found on the internet. Don't work.
end sub

Above names that dropdown but I can't use the name to code anything..

Community
  • 1
  • 1
jvh
  • 143
  • 2
  • 13
  • possible duplicate of [Programatically add ComboBox in VBA (Excel)](http://stackoverflow.com/questions/17675761/programatically-add-combobox-in-vba-excel) – Daniel Aug 27 '15 at 18:44

2 Answers2

1

What about this (untested, but logical):

Private Sub Workbook_Open()
    Dim objDropDown as DropDown
    Set objDropDown = Sheet10.DropDowns.Add(280, 70, 200, 20)
    ... etc ...
End Sub
Joe
  • 122,218
  • 32
  • 205
  • 338
1

Typically methods like Add in excel return an instance to the object. So for example, you can do the following:

Dim myDropdown As DropDown
Set myDropdown = Sheet10.DropDowns.Add(280, 70, 200, 20)
myDropdown.Name = "cmbTest"
myDropdown.AddItem "Hi"

Your frustration is understandable as I had difficulty finding any documentation for this. However, this blog post: VBA For Excel's Form Control Combo Boxes, seems to have some potentially useful examples.

Daniel
  • 12,982
  • 3
  • 36
  • 60
  • Well I feel kinda dumb now, I tried the exact thing you said except for Dim myDropdown As DropDown. I had something like Dim myDropdown As Object or Dim myDropdown As MSForms.ComboBox. Neither of which worked obviously. – jvh Aug 27 '15 at 18:46
  • It also works with `dim as Variant` or 'Object'. No idea why it didn't work for you. – Daniel Aug 27 '15 at 18:48