Calling a List to Add to a ComboBox Dropdown in VBA

Question

I have many different Modules that need to call a list of options to add to a dropdown box. I am trying to figure out how I can add what is essentially a dictionary with a list of items to add to a dropdown in one module, and then call that module for the certain dropdown boxes in other modules. Here is an example:

In my "Dictionary" module I have this list:

Item 1, Item 2, Item 3, Item 4...

In another module I want to add this list to ComboBox3, for example. But in another module I want to populate both ComboBox1 and ComboBox2 with this list, etc.

Any ideas?

Here is something I tried, but doesn't work...

In the dictionary module:

Public Sub AllOptions() 

     'This is a list of all the possible ComboBoxes throughout all modules.

    ComboBoxList = Array(CStr(ComboBox1), CStr(ComboBox2), CStr(ComboBox3), CStr(ComboBox4), CStr(ComboBox5)) 


    For Each Ky In ComboBoxList 
        On Error Resume Next 
        If Ky.Listbox = 0 Then 
            With Ky 
                .AddItem "Item 1" 
                .AddItem "Item 2"... 
            End With 
        End If 
    Next 

End Sub

Then in the module that I want to call this I have:

Private Sub ComboBox2_DropButtonClick() 


    Call Module9.AllOptions 


End Sub

Thanks!

The list you're populating goes away when the subroutine ends, and you never do anything with it other than fill it and then discard it. Read the documentation on *variable scope and lifetime*. You should be creating a list and passing it in to the sub to populate for you as needed. — Ken White, Dec 15 '15 at 00:29
Yeah, what Ken says. Also, look into Functions, as they can return data (although subs can change their inputs and if they are variables in the calling sub, the variable will also change... don't do this though. I addition, i'm still not sure what you are trying to do. Having said that, if you plan to store objects, assign them to a collection, not an array. Its easier in the long run. — Andy Wynn, Dec 15 '15 at 02:44

Calling a List to Add to a ComboBox Dropdown in VBA

0 Answers0