I posted an example within the workbook with expected input and output. Please let me know if it's not clear enough. From the form code below with explanation following:
Private Sub UserForm_Initialize()
Dim x
Dim g&, h&, i&, j&
x = Sheets(msSHEET_LIN).Range("A1").CurrentRegion.Value
With Me.cboLiner1
For g = 2 To UBound(x)
If Not IsEmpty(x(g, 4)) Then
.AddItem x(g, 4) 'Liner
.List(h, 1) = x(g, 5) 'Liner code
h = h + 1
End If
Next g
End With
End Sub
Private Sub cboLiner1_Change()
Dim x
Dim i&, LinerCode$
If Me.cboLiner1.ListIndex = -1 Then Exit Sub
Me.cboStrokes1.Enabled = True
With Me.cboLiner1
LinerCode = .List(.ListIndex, 1)
End With
With Me.cboStrokes1
.Clear
For i = 2 To UBound(x)
If x(i, 3) = LinerCode Then .AddItem x(i, 1) ''x(i, 3) --> SkLine_Code
Next i
End With
End Sub
Private Sub cboStrokes1_Change()
Dim val as Range
If Me.cboStrokes1.ListIndex = -1 Then Exit Sub
Set val = Worksheets(msSHEET_LIN).Columns(1).Find(cboStrokes1.Value, LookIn:=xlValues, Lookat:=xlWhole)
txtPmpVol1.Value = val.Offset(0, 1)
End Sub
A list of 13 pump dimensions (D). Each dimension has an associated code# (1-13) in (E). Another list of pump housing dimensions are in (A) (13 choices). The fact there are 13 pump options and 13 housing
options is purely coincidental. There are 13 pumps and 13 housings, so there are possible 169 unique outputs with selection of (D & A). These 169 outputs can be parsed into 13 groups in (A) using the associated
code# from (E). With any configuration of (D) and (A), output volume (B) changes. Each (B) is directly referenced to (A) through the code# from (E).
e.g. If input 4.5" (D) is selected in combobox1, the result in combobox2 is all housings (A) with code (C) matching code (E). Next, when a housing (A) is selected in combobox2, an output value from (B) populates the textbox.
Bookmarks