Dependent Comboboxes

J

jnd082505

New Member
Joined
Aug 9, 2019
Messages
1
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!

[TABLE="class: grid, width: 1062"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3, align: center"] CR123[/TD]
[TD="colspan: 2, align: center"]AA[/TD]
[TD="colspan: 3, align: center"]AAA Lithium[/TD]
[TD="colspan: 3, align: center"]AA Lithium[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1 cell[/TD]
[TD]2 cell +[/TD]
[TD]4 cell[/TD]
[TD]3 cell[/TD]
[TD]3 cell +[/TD]
[TD]3 cell[/TD]
[TD]3 cell +[/TD]
[TD]6 cell[/TD]
[TD]1 cell[/TD]
[TD]3 cell[/TD]
[TD]4 cell[/TD]
[/TR]
[TR]
[TD]Watch[/TD]
[TD="align: center"][/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"][/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]130[/TD]
[TD="align: center"][/TD]
[TD="align: center"]155[/TD]
[TD="align: center"]210[/TD]
[/TR]
[TR]
[TD]Remote[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]200[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]105[/TD]
[TD="align: center"][/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]240[/TD]
[/TR]
[TR]
[TD]Calculator[/TD]
[TD="align: center"]105[/TD]
[TD="align: center"]210[/TD]
[TD="align: center"]420[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]365[/TD]
[TD="align: center"]480[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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.

Code: 
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.


<caption>LEGO HTML</caption><colgroup><col style="width: 54px"><col width="128"><col width="139"><col width="113"><col width="110"></colgroup><tbody>
[TH="bgcolor: #A6A6A6"] A [/TH]
[TH="bgcolor: #A6A6A6"] B [/TH]
[TH="bgcolor: #A6A6A6"] C [/TH]
[TH="bgcolor: #A6A6A6"] D [/TH]

[TH="bgcolor: #A6A6A6"] 1 [/TH]
[TD="bgcolor: #FFFFFF"] Item [/TD]
[TD="bgcolor: #FFFFFF"] Battery [/TD]
[TD="bgcolor: #FFFFFF"] Cell [/TD]
[TD="bgcolor: #FFFFFF"] Life [/TD]

[TH="bgcolor: #A6A6A6"] 2 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AA [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell [/TD]
[TD="bgcolor: #FFFFFF"] 250 [/TD]

[TH="bgcolor: #A6A6A6"] 3 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AA [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 250 [/TD]

[TH="bgcolor: #A6A6A6"] 4 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 1 cell [/TD]
[TD="bgcolor: #FFFFFF"] 120 [/TD]

[TH="bgcolor: #A6A6A6"] 5 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell [/TD]
[TD="bgcolor: #FFFFFF"] 365 [/TD]

[TH="bgcolor: #A6A6A6"] 6 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 480 [/TD]

[TH="bgcolor: #A6A6A6"] 7 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell [/TD]
[TD="bgcolor: #FFFFFF"] 110 [/TD]

[TH="bgcolor: #A6A6A6"] 8 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 110 [/TD]

[TH="bgcolor: #A6A6A6"] 9 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 6 cell [/TD]
[TD="bgcolor: #FFFFFF"] 220 [/TD]

[TH="bgcolor: #A6A6A6"] 10 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 1 cell [/TD]
[TD="bgcolor: #FFFFFF"] 105 [/TD]

[TH="bgcolor: #A6A6A6"] 11 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 2 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 210 [/TD]

[TH="bgcolor: #A6A6A6"] 12 [/TH]
[TD="bgcolor: #FFFFFF"] Calculator [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 420 [/TD]

[TH="bgcolor: #A6A6A6"] 13 [/TH]
[TD="bgcolor: #FFFFFF"] Remote [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell [/TD]
[TD="bgcolor: #FFFFFF"] 180 [/TD]

[TH="bgcolor: #A6A6A6"] 14 [/TH]
[TD="bgcolor: #FFFFFF"] Remote [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 240 [/TD]

[TH="bgcolor: #A6A6A6"] 15 [/TH]
[TD="bgcolor: #FFFFFF"] Remote [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 6 cell [/TD]
[TD="bgcolor: #FFFFFF"] 105 [/TD]

[TH="bgcolor: #A6A6A6"] 16 [/TH]
[TD="bgcolor: #FFFFFF"] Remote [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 200 [/TD]

[TH="bgcolor: #A6A6A6"] 17 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] AA [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 100 [/TD]

[TH="bgcolor: #A6A6A6"] 18 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell [/TD]
[TD="bgcolor: #FFFFFF"] 155 [/TD]

[TH="bgcolor: #A6A6A6"] 19 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] AA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 210 [/TD]

[TH="bgcolor: #A6A6A6"] 20 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 3 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 65 [/TD]

[TH="bgcolor: #A6A6A6"] 21 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] AAA Lithium [/TD]
[TD="bgcolor: #FFFFFF"] 6 cell [/TD]
[TD="bgcolor: #FFFFFF"] 130 [/TD]

[TH="bgcolor: #A6A6A6"] 22 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 2 cell + [/TD]
[TD="bgcolor: #FFFFFF"] 85 [/TD]

[TH="bgcolor: #A6A6A6"] 23 [/TH]
[TD="bgcolor: #FFFFFF"] Watch [/TD]
[TD="bgcolor: #FFFFFF"] CR123 [/TD]
[TD="bgcolor: #FFFFFF"] 4 cell [/TD]
[TD="bgcolor: #FFFFFF"] 180 [/TD]

</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:
Code: 
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:
Code: 
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"
 
Upvote 0
small change

Code: 
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
 
Upvote 0
Cleaned up the M code a bit too. Cut down the steps by a third.

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
280
Cquake
C
M
  • Question Question
Match combobox name on userform with shee name and fill empty cell
Replies
0
Views
247
Maklil
M
S
  • Question Question
'Fun' Excel Data Analytics Puzzle
Replies
0
Views
383
sjordan
S
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
199
ngochien251088
N
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
589
madi1004
M

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top