VBA - ActiveX - Issues populating the 2nd combobox with all of the unique values defined by comboBox 1

E

ectiger86

Board Regular
Joined
Aug 27, 2014
Messages
67
I think I may have made my last post to complicated and confusing. I'm going to try and break down the issues one by one and hopefully I can get some help that way.

Here is a link to my other thread if you care to look at what I have done so far and what I trying to accomplish [url]http://www.mrexcel.com/forum/excel-questions/870746-very-detailed-visual-basic-applications-activex-cascading-dependent-comboboxs-issues-any-all-input-welcome.html


[/URL]
Issue # 1

This is installed on the worksheet("CHART") and is used to fill the second Combobox called cmbSub based on the selection in the first ComboBox cmbRent. I am trying to learn while I am working

Its not working properly.


Code: 
Private Sub cmbRent_change()

myVal = Me.cmbRent.Value

'loop thru col B
lr = ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

'clear cmbSub
Me.cmbSub.Clear


'loop thru
For X = 2 To lr
    If myVal = ThisWorkbook.Sheets("DATA").Cells(X, 2) Then
        'add to combobox
       Me.cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(X, 2)
  End If
   
Next X
    Me.cmbSub.ListIndex = -1
End Sub



What it does do is fills the second ComboBox based on the selection in the first combobox but only when the selection in the first combobox matches exactly the coresponding choices in the the "SUB-CATEGORY" range. For example, When Accumulator is selected for the Category, it shows Accumulator multiple times as a Sub-Category option. However when a Category such as Flange,Adapter or Gate Valve is chosen that has multiple coresponding unique Sub-Category options it does not fill anything in to the "Sub-Category" combobox and remains blank.

I am needing the "Sub-Category" combobox to be filled with all of the UNIQUE related Sub-Category choices so I can drill down even further based on the selection the user makes.
[TABLE="class: cms_table_cms_table_grid, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="class: cms_table_cms_table_grid, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="class: cms_table_cms_table_grid, align: center"]
<tbody>[TR]
[TD]Category
[/TD]
[TD]SUB-CATEGORY
[/TD]
[TD]LOCATION
[/TD]
[TD]CUSTOMER
[/TD]
[TD]TOTAL
[/TD]
[/TR]
[TR]
[TD](All)[/TD]
[TD](All)[/TD]
[TD](All)[/TD]
[TD](All)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Accumulator[/TD]
[TD]Accumulator[/TD]
[TD]CAR[/TD]
[TD]XXXX
[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Accumulator[/TD]
[TD]Accumulator[/TD]
[TD]WCND[/TD]
[TD]YYYY[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Accumulator[/TD]
[TD]Accumulator[/TD]
[TD]WCND[/TD]
[TD]YYYY[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Flange, Adapter
[/TD]
[TD]11"5M
[/TD]
[TD]BCA[/TD]
[TD]YYYY[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Flange, Adapter[/TD]
[TD]11"5M[/TD]
[TD]ODTX[/TD]
[TD]XXXX
[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Flange, Adapter[/TD]
[TD]13-5/8"10M[/TD]
[TD]CAR[/TD]
[TD]AAAA[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Flange, Adapter[/TD]
[TD]WECO
[/TD]
[TD]CPA[/TD]
[TD]AAAA[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 1-13/16"10M
[/TD]
[TD]CPA[/TD]
[TD]BBBB[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 1-13/16"10M[/TD]
[TD]OKC[/TD]
[TD]BBBB[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 7-1/16"10M[/TD]
[TD]OKC[/TD]
[TD]CCCC[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 7-1/16"10M[/TD]
[TD]CPA[/TD]
[TD]DDDD[/TD]
[TD]$x.xx
[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 2-1/16"5M[/TD]
[TD]WCND[/TD]
[TD]DDDD[/TD]
[TD]$x.xx[/TD]
[/TR]
[TR]
[TD]Gate Valve[/TD]
[TD]HWO - 2-1/16"5M[/TD]
[TD]CAR[/TD]
[TD]AAAA[/TD]
[TD]$x.xx[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
solved with just one little change.

Code: 
   [TABLE]
<colgroup><col style="width:74pt" span="2" width="99">  </colgroup><tbody>[TR]
   [TD="width: 198, colspan: 2"]Private Sub cmbRent_change()[/TD]
  [/TR]
  [TR]
   [TD]    [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]    MyVal = Me.cmbRent.Value[/TD]
  [/TR]
  [TR]
   [TD]    [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]    'loop thru col B[/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]    lr =   ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row[/TD]
  [/TR]
  [TR]
   [TD]    [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]    'clear cmbSub[/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]      ThisWorkbook.Sheets("CHART").cmbSub.Clear[/TD]
  [/TR]
  [TR]
   [TD]    [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD]    [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD]      'loop thru[/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD]      For x = 2 To lr[/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]        If MyVal =   ThisWorkbook.Sheets("DATA").Cells(x, 1) Then[/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]            'add to combobox[/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]         ThisWorkbook.Sheets("CHART").cmbSub.AddItem   ThisWorkbook.Sheets("DATA").Cells(x, 2)[/TD]
  [/TR]
  [TR]
   [TD]        End If[/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD]       [/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD]      Next x[/TD]
   [TD][/TD]
  [/TR]
  [TR]
   [TD="colspan: 2"]            ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1[/TD]
  [/TR]
  [TR]
   [TD]      End Sub[/TD]
   [TD][/TD]
  [/TR]
 </tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VERY DETAILED!! VBA - ActiveX Cascading Dependent ComboBox's Issues - ANY AND ALL INPUT IS WELCOME
Replies
8
Views
2K
ectiger86
E
E
  • Question Question
VBA - Active X ComboBox #2 only showing 1 unique value instead of All unique values based on Combobox 1 selection
Replies
5
Views
981
ectiger86
E

Forum statistics

Threads
1,223,970
Messages
6,175,699
Members
452,667
Latest member
vanessavalentino83

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