Array and Variable Help

[nnicolazzi]

Hello,

Goal = To have a combo box add an item for each cell in a range. I have a userform where a user can select a material, and based upon the material selected another combo box is loaded with color choices that they can select from. But I want this to happen automatically when I add or delete items from the color and material ranges. When I add a new material and color options I want the combo boxes to update without me having to add more code.

I have a sheet with ranges named for materials and color. The materials range is a list of different materials. The color ranges are named "material_Color" for each type of material. Basically a list of material and a list of colors for each material.

When a material is selected in cmb1 I want cmb2 to have a list of colors for that type of material. Those colors will come from the material_Color lists.

I've set up the ranges so that when an item is added (another color) it is part of the named range.

I've set up a variable that reads the value of cmb1

I've set up another variable that outputs the value of cmb1(material) and adds "_Color" (the material_Color should be the range of cells associated to my material_Color list)

I have a For Each loop that looks at each cell in my material_Color range and adds the item to my cmb.

For some reason when I run the code it outputs the name of my range into the cmb instead of adding what is in the cells in the range. (it fills the cmb with material_Color instead of the list of colors associated with material_Color.

I've added the code below as well as a link to a video so you can see whats going on. I feel like i'm close but I dont know why its outputting the name of the range instead of the contents in the range..

Any help is greatly appreciated!!!

Thank you in advance!!!\

Private Sub cmbTopColor_Enter()<o></o>
Dim strCname(20) As Variant<o></o>
Dim srtTopMat As Variant<o></o>
strTopMat = cmbTopMat.Value<o></o>
'Set strCname = "[" & strTopMat & "_Color]"<o></o>
strCname(UBound(strCname)) = "[" & strTopMat &"_Color]"<o></o>
For Each Cell In strCname [Formica_Color]<o></o>
cmbTopColor.AddItem Cell<o></o>
Next Cell<o></o>
End Sub


<o></o>
https://www.screencast.com/t/mqaRQroa<o></o>

 

[nnicolazzi]

When i say i want it to automatically I mean that I want to be able to edit my lists and have my userform update when its being used

 

[nnicolazzi]

Private Sub cmbTopColor_Enter()<o></o>
Dim strCname(20) As Variant<o></o>
Dim srtTopMat As Variant<o></o>
strTopMat = cmbTopMat.Value<o></o>
'Set strCname = "[" & strTopMat & "_Color]"<o></o>
strCname(UBound(strCname)) = "[" & strTopMat &"_Color]"<o></o>
For Each Cell In strCname '[Formica_Color] NOTE: MY FOR LOOP WORKS IF I USE "[Formica_Color]" but does not when I use strCname.I set the strCname to output "[Formica_Color]" why arent strCname and Formica_Color the same?<o></o>
cmbTopColor.AddItem Cell<o></o>
Next Cell<o></o>
End Sub<o></o>

 

[StephenCrump]

This is a bit of a guess, but I think this is what you're trying to do?

Dim strCname As String

strCname = cmbTopMat.Text & "_Color"  'cmbTopMat.Text is "Formica"?

For Each Cell In Range(strCname)    'You have an Excel range called Formica_Color?
    cmbTopColor.AddItem Cell.Value
Next Cell