Calling a List to Add to a ComboBox Dropdown

[hunter21188]

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!

[John Wilson]

Are the ComboBoxes in a UserForm or are they just added onto slides?

If they are on slides you cannot reference them as "ComboxBox1" etc as duplicate names are allowed on different slides.

It's not clear how skilled you are and it will matter because this is probably going to be slightly confusing. Be good if you give some idea of your skill level.

See if this sample helps direct you a different path!

[hunter21188]

Thanks for the reply, John. Each ComboBox is in a UserForm. I just started learning VBA about a month ago, so much of this is still fairly new to me. However, I do have a fair amount of experience with Python, if that means anything...

Thanks for taking the time to work up that example! Since I have the ComboBoxes in User Forms, I feel like there is something simple I am missing. This is what I have now, and I think the list in the separate module isn't able to be read by the sub I am in.

In a module (Module9) I have:



'Do I need something here to make this sub accessible outside of this Module?
Sub AllOptions()
Public strlist As String

'Can I list these like this, or should I put them in an Array?
strlist = "Item 1,Item 2,Item 3,Item 4,Item 5,Item 6"


End Sub


In my UserForm I have this:


Private Sub ComboBox3_DropButtonClick()
Call Module9.AllOptions


For Each Name In strlist
If ComboBox3.ListCount = 0 Then
With ComboBox3
.AddItem Name
End With
End If
Next


End Sub


Thanks!

[John Wilson]

You should not use the drop button code it will only give you a headache each time you use the combo box. You are not allowed to declare PUBLIC variables inside a Subroutine they must be outside.

In the main module




Public rayNames() As String

Sub AllOptions()
Dim strlist As String
'Put them in an Array declared as Public
strlist = "Item 1,Item 2,Item 3,Item 4,Item 5,Item 6"
rayNames = Split(strlist, ",")
End Sub

'code to open form (repeat for each)

Sub form()
Call AllOptions
Load UserForm1
UserForm1.Show
End Sub




Rename the comboBox. You can use the same name on each form.
In the Initialize method of each form



Private Sub UserForm_Initialize()
Dim L As Long
With Me.Combo_Names
.Clear
For L = 0 To UBound(rayNames)
.AddItem rayNames(L)
Next L
.Value = rayNames(0)
End With
End Sub

[hunter21188]

Awesome! Thanks, John. Works perfectly!