Dependent Comboboxes

[jnd082505]

Good afternoon! I'm still very very new to VBA and user forms in Excel so I'm hoping to get some help. I'd like to create a user form that would allow for the selection of 2 independent defined variables and produce all possible results. Example: I have a remote that can use multiple battery types. I want to select a technology type ("Remote" in this case), a number of hours I'd like the remote to last, and click a button to display all battery options that will satisfy my requirements. Below is my chart. I would greatly appreciate any help! Thank you!

	CR123	AA	AAA Lithium	AA Lithium
	1 cell	2 cell +	4 cell	3 cell	3 cell +	3 cell	3 cell +	6 cell	1 cell	3 cell	4 cell
Watch		85	180		100		65	130		155	210
Remote			200					105		180	240
Calculator	105	210	420	250	250	110	110	220	120	365	480

<tbody>
</tbody>

 

[lrobbo314]

So here is what I've done. First off, I used PowerQuery to transform your table into a proper data set. The following M code can be used for the transformations.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeI = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"CR123", type any}, {"Column2", type any}, {"Column3", type any}, {"AA", type any}, {"Column4", type any}, {"AAA Lithium", type any}, {"Column5", type any}, {"Column6", type any}, {"AA Lithium", type any}, {"Column7", type any}, {"Column8", type any}}),
    Demote = Table.DemoteHeaders(TypeI),
    TypeII = Table.TransformColumnTypes(Demote,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}}),
    Transposed = Table.Transpose(TypeII),
    AddedCustom = Table.AddColumn(Transposed, "Custom", each if Text.Contains([Column1],"Column") then null else [Column1]),
    FilledDown = Table.FillDown(AddedCustom,{"Custom"}),
    RemovedColumns = Table.RemoveColumns(FilledDown,{"Column1"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Custom", "Column2", "Column3", "Column4", "Column5"}),
    ReplacedValue = Table.ReplaceValue(ReorderedColumns,null,"Cell",Replacer.ReplaceValue,{"Column2"}),
    PromotedHeaders = Table.PromoteHeaders(ReplacedValue, [PromoteAllScalars=true]),
    TypeIII = Table.TransformColumnTypes(PromotedHeaders,{{"Column1", type text}, {"Cell", type text}, {"Watch", Int64.Type}, {"Remote", Int64.Type}, {"Calculator", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(TypeIII, {"Column1", "Watch", "Remote", "Calculator"}, "Attribute", "Value"),
    RemovedColumnsII = Table.RemoveColumns(Unpivot,{"Attribute"}),
    ReorderedColumnsII = Table.ReorderColumns(RemovedColumnsII,{"Watch", "Remote", "Calculator", "Column1", "Value"}),
    UnpivotedII = Table.UnpivotOtherColumns(ReorderedColumnsII, {"Column1", "Value"}, "Attribute", "Value.1"),
    ReorderedColumnsIII = Table.ReorderColumns(UnpivotedII,{"Attribute", "Column1", "Value", "Value.1"}),
    RenamedColumns = Table.RenameColumns(ReorderedColumnsIII,{{"Attribute", "Item"}, {"Column1", "Battery"}, {"Value", "Cell"}, {"Value.1", "Life"}})
in
    RenamedColumns

You will need to name the query 'Raw', because that name will be referenced in the VBA later on. It will now look like this.

	A	B	C	D
1	Item	Battery	Cell	Life
2	Calculator	AA	3 cell	250
3	Calculator	AA	3 cell +	250
4	Calculator	AA Lithium	1 cell	120
5	Calculator	AA Lithium	3 cell	365
6	Calculator	AA Lithium	4 cell	480
7	Calculator	AAA Lithium	3 cell	110
8	Calculator	AAA Lithium	3 cell +	110
9	Calculator	AAA Lithium	6 cell	220
10	Calculator	CR123	1 cell	105
11	Calculator	CR123	2 cell +	210
12	Calculator	CR123	4 cell	420
13	Remote	AA Lithium	3 cell	180
14	Remote	AA Lithium	4 cell	240
15	Remote	AAA Lithium	6 cell	105
16	Remote	CR123	4 cell	200
17	Watch	AA	3 cell +	100
18	Watch	AA Lithium	3 cell	155
19	Watch	AA Lithium	4 cell	210
20	Watch	AAA Lithium	3 cell +	65
21	Watch	AAA Lithium	6 cell	130
22	Watch	CR123	2 cell +	85
23	Watch	CR123	4 cell	180

<caption>LEGO HTML</caption><colgroup><col style="width: 54px"><col width="128"><col width="139"><col width="113"><col width="110"></colgroup><tbody>
</tbody>

I loaded this transformed table to a new worksheet 'Worksheet2'.

Then I made a UserForm with a combobox for watch, calc, remote. 2 textboxes for battery life. 1 for the low hours and 1 for the high. So, for example, I select Calculator from the combobox. Then say I want batteries that will last from 200-400 hours, 200 will go in textbox1, 400 will go in textbox2.

Then I have a button. The button code will filter the transformed table to show the data that matches the criteria from the combobox and textboxes.

The code for the UserForm Initialize Event:

Private Sub UserForm_Initialize()
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim AR() As Variant: AR = Sheets("Sheet2").ListObjects("Raw").DataBodyRange.Value
Dim Product() As Variant


For i = LBound(AR) To UBound(AR)
    If Not AL.contains(AR(i, 1)) Then AL.Add AR(i, 1)
Next i


Me.ComboBox1.List = AL.toarray
End Sub

The code for the command button:

Dim Product As String: Product = Me.ComboBox1.Value
Dim tFrom As Single: tFrom = Me.TextBox1.Value
Dim tTo As Single: tTo = Me.TextBox2.Value
Dim LO As ListObject: Set LO = Sheets("Sheet2").ListObjects("Raw")
Dim AR() As Variant


LO.Range.AutoFilter 1, Product, xlAnd
LO.Range.AutoFilter 4, ">=100", xlAnd, "<=190"

 

[lrobbo314]

small change

Private Sub CommandButton1_Click()Dim Product As String: Product = Me.ComboBox1.Value
Dim tFrom As Single: tFrom = Me.TextBox1.Value
Dim tTo As Single: tTo = Me.TextBox2.Value
Dim LO As ListObject: Set LO = Sheets("Sheet2").ListObjects("Raw")
Dim AR() As Variant


LO.Range.AutoFilter 1, Product, xlAnd
[COLOR=#0000ff][B]LO.Range.AutoFilter 4, ">=" & tFrom, xlAnd, "<=" & tTo[/B][/COLOR]


End Sub

 

[lrobbo314]

Cleaned up the M code a bit too. Cut down the steps by a third.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Transposed = Table.Transpose(Demote),
    AddedCustom = Table.AddColumn(Transposed, "Custom", each if Text.Contains([Column1],"Column") then null else [Column1]),
    FilledDown = Table.FillDown(AddedCustom,{"Custom"}),
    RemovedColumns = Table.RemoveColumns(FilledDown,{"Column1"}),
    ReplacedValue = Table.ReplaceValue(RemovedColumns,null,"Cell",Replacer.ReplaceValue,{"Column2"}),
    PromotedHeaders = Table.PromoteHeaders(ReplacedValue, [PromoteAllScalars=true]),
    Unpivot = Table.UnpivotOtherColumns(PromotedHeaders, {"Column5", "Cell"}, "Attribute", "Value"),
    Rename = Table.RenameColumns(Unpivot,{{"Column5", "Battery"}, {"Value", "Life"}, {"Attribute", "Item"}}),
    Reorder = Table.ReorderColumns(Rename,{"Item", "Battery", "Cell", "Life"}),
    Sort = Table.Sort(Reorder,{{"Item", Order.Descending}, {"Battery", Order.Ascending}, {"Life", Order.Ascending}})
in
    Sort