VERY DETAILED!! VBA - ActiveX Cascading Dependent ComboBox's Issues - ANY AND ALL INPUT IS WELCOME

[ectiger86]

Hey Gurus,

I have been grinding my way through this learning process for about 3-4 weeks now and have used alot of resources. Any and All input is welcome.

Any suggestions on Code for any step in this is welcome. I'm really trying to begin learning VBA and impress my Boss and raise my value at work by learning and adding a couple of notches in my belt. Thanks in advance!

Part 1 - Whats, What so far

Part 2 - ISSUES

Currently at this point in this Project the following sources, along with many other random sources, were used to get me this far:
https://www.youtube.com/watch?v=1HH5iBdG7xw
https://www.youtube.com/watch?v=yMO_wCZgQbc
https://www.youtube.com/watch?v=FW89ITnAq2Q

The thing that makes this a little more difficult is the fact that I am not wanting to create separate columns for each "Sub-Category" and list all of its options. I would have been done 3 weeks ago if that was the case.

The data is from Sheets("DATA") and the combobox's are on Sheets("CHART"). There DATA sheet is always going to be changing so my drop downs are based on Dynamic Named Ranges.

Dynamic Named Ranges

catagorymain =OFFSET(DATA!$A$2,0,0,COUNTA(DATA!$A$2:$A$9890))
subcatagorymain =OFFSET(DATA!$B$2,0,0,COUNTA(DATA!$B$2:$B$9890))
 locationmain =OFFSET(DATA!$C$2,0,0,COUNTA(DATA!$C$2:$C$9890))
custmain =OFFSET(DATA!$D$2,0,0,COUNTA(DATA!$D$2:$D$9890))

Dynamic Named Ranges catagorymain subcatagorymain locationmain custmain [TABLE="class: grid, width: 500, 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]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The Data is put into PivotTable1 on ThisWorkBook.Sheets("Pivot") and a PivotChart was created and is on ThisWorkBook.Sheets("CHART").

I have 4 ComboBoxes that I want to use to drill down. As the user choses the category in ComboBox1(cmbRent) a macro changes the PivotFilter on ("Pivot") It changes the PivotChart on ("CHART") using named ranges [selCat] and [fltCat].

This fills my first ActiveX ComboBox called cmbRent and produces a unique list.

 Sub FillCombos()

    Dim TABLE As Worksheet
    Dim DATA As Worksheet
    Set ch = ThisWorkbook.Sheets("CHART")
    Set da = ThisWorkbook.Sheets("DATA")
    
' Last Row
daLR = da.Cells(Rows.Count, 1).End(xlUp).Row
FirstTime = True

' Loop thru rows
For X = 2 To daLR
    If da.Cells(X, 1) <> "" And (InStr(blah, "|" & da.Cells(X, 1) & "|") = 0) Then
        If FirstTime = True Then
            FirstTime = False
         blah = "|" & blah & da.Cells(X, 1) & "|"
     Else
        blah = blah & da.Cells(X, 1) & "|"
    End If
End If
    

Next X

myArray = Split(blah, "|")

Sheet7.cmbRent.Clear

 For Each cell In myArray
    If cell <> "" Then
        Sheet7.cmbRent.AddItem (cell)
    End If
Next cell
      ThisWorkbook.Sheets("CHART").cmbRent.ListIndex = 0
   
   
   End Sub

It is called at the time the Workbook is opened. it is located on "This Workbook".

 Private Sub Workbook_Open()
Call FillCombos
End Sub

This is the code that is used to change the pivotTable and PivotChart based on the selction. My ComboBoxes are linked to the name ranges on ("CHART") so when a selction is made it populates in the respective named range. I am having issues with this code not working all of the time, maybe because i have be messing with it so much, HA! I would like some help with this one as well.

 Sub FilterCat()
 
Dim pt As PivotTable
Dim ws As Worksheet


Set pt = Sheets("Pivot").PivotTables("PivotTable1")
Set ws = ThisWorkbook.Sheets("CHART")

   [fltCat] = [selCat]

If ws.Range("selCat") = "(All)" Then

    pt.PivotFields("CATEGORY").ClearAllFilters
End If

Here is the ("CHARTS") page

And here is the ("Pivot") page

To be Cont.

 

[ectiger86]

ISSUES

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. Its not working properly.

 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 with 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 UNIQUE related Sub-Category choices so I can drill down even further based on the selection the user makes.

[TABLE="class: 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]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to have only the coresponding UNIQUE Locations and Customers for each Category and Sub-Category selection to populate in the comboboxes I have created for them.

They as well will be able to drill down based on all of the previous selections and thus showing the proper graph based on the filter macros.



This is where i stand so far.

This is a major learning/work project for me. I am creating this without my bosses knowledge in hopes to impress him a little but maintain and or raise my value in our company right now, especially with the volatile oil prices which greatly effect employment and unemployment throughout.

 

[ectiger86]

Bump for the folks across the world that are just now having their second cup of coffee.

 

[ectiger86]

Bump for the "this side of the world" gurus that are up and excelling....

 

[ectiger86]

Bump

 

[ectiger86]

Bump

 

[ectiger86]

Bump

 

[jeffreyweir]

Here's an easy non-VBA way that gets around the need for comboboxes: Use Slicers, with the 'Hide items with no data' option in the Slicer Settings dialog checked for each of them. When that setting is checked, only relevant items appear in the slicers.

 

[ectiger86]

I originally was using slicers and your right it worked great. However, With the amount of information that the user would have to choose from that related to each option, there was way to many slicers needed and slicer buttons. It was too crowded and busy.

Combo boxes are the cleaner better solution, Plus I wanted to start getting into the challenge of learning some coding !