Looping Through Select Cases

[G0DSL3FTHAND]

Hello All,

I have a independent combobox and a dependent combobox, the dependent combobox depends on the information provided in the first independent combobox.

I know there is the select case to make fill the dependent combobox. Here is a brief overview what my code does.

I have two different user forms one for new materials and the second is for existing materials. The new material user form populates the storage sheet with the different materials. the material information comes in two forms a 4 digit number and a 2 digit number. the new material form determines if the 4 digit number already exists, and if it does it adds the 2 digit number under the column with the 4 digit column information. For Example the input is 5600 and 96, if 5600 already exist then 96 is added to the column for the information containing 5600, but if 5600 is new it becomes the header of a new column and 96 is added to the new 5600 column. the new material form also defines the name for the the new column as No.5600.

The problem I am having is that the independent list is always growing, and since the independent list is always growing so is the number of cases. So i tried to use a for loop in conjunction with select case to try and have the cases always growing. however, when the code is run the dependent combobox does not have any of the information under the name listed in the independent combobox.

Here is my code:
Private Sub UserForm_Initialize()
Dim z As Integer
Dim x As Integer
Dim ws As Worksheet
Dim wss As Worksheet
Set ws = Sheets("Storage")
Set wss = Sheets("Staging")
Me.myctl5.AddItem "Static Preload"
Me.myctl5.AddItem "Vertical Fatigue"
Me.myctl5.AddItem "Combination Test"


Me.myctl2.Clear
'Number of Columns with Numbers in them
ws.Range("B2").Formula = "=COUNT(A1:CC1)"
'z is the column location offset by one
z = ws.Range("B2").Value + 1


For x = 3 To z
Select Case myctl1
Case ws.Cells(1, z).Value
RowSource = "No." & ws.Cells(1, z).Value
End Select
Next

End Sub

Example table:

4 digit	4546	5600	5400	4500
4546	12	32	54	58
5600	15	56	65	25
5400	32	54	22	55
4500		96

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to MrExcel.
maybe something like

Private Sub ComboBox1_Click()
   Dim Fnd As Range
   Me.ComboBox2.Clear
   Set Fnd = Range("1:1").find(Me.ComboBox1.Value, , , xlWhole, , , , , False)
   If Fnd Is Nothing Then
      MsgBox Me.ComboBox1.Value & " not found"
   Else
      Me.ComboBox2.List = Range(Fnd.Offset(1), Fnd.End(xlDown)).Value
   End If
End Sub