VBA - Active X ComboBox #2 only showing 1 unique value instead of All unique values based on Combobox 1 selection

E

ectiger86

Board Regular
Joined
Aug 27, 2014
Messages
67
I got my second ActiveX comboBox to populate based on the 1st comobox selection but now I need only unique values to show in Combobox#2

Here is my code for filling combobox2

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]




I modified it some based on the parts in my code on my first combobox that fills it with unique items only but now It only gives one item in combobox 2 and not multiple unique items based on the selection in combobox 1.

Code: 
 Private Sub cmbRent_change()
    
  MyVal = Me.cmbRent.Value
    
    'loop thru col B
    lr = ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
       
        
    'loop thru
    For x = 2 To lr
        If MyVal = ThisWorkbook.Sheets("DATA").Cells(x, 1) Then
        
            If ThisWorkbook.Sheets("DATA").Cells(x, 2) <> "" And (InStr(blah, "|" & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|") = 0) Then
        If FirstTime = True Then
            FirstTime = False
         blah = "|" & blah & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|"
     Else
        blah = blah & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|"
    End If
End If
        'Clear cmbSub
        ThisWorkbook.Sheets("CHART").cmbSub.Clear
        
            'add to combobox
       ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
      End If
       
    Next x
      
    
    myArray = Split(blah, "|")
      
    
    For Each cell In myArray
    If cell <> "" Then
        
         ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
      End If
    
    
    Next cell
    
        
          ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1
    End Sub


any sugestions?
 
Sort by date Sort by votes
ectiger86 said:
I modified it some based on the parts in my code on my first combobox that fills it with unique items only but now It only gives one item in combobox 2 and not multiple unique items based on the selection in combobox 1.

Code: 
 Private Sub cmbRent_change()
    
  MyVal = Me.cmbRent.Value
    
    'loop thru col B
    lr = ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
       
        
    'loop thru
    For x = 2 To lr
        If MyVal = ThisWorkbook.Sheets("DATA").Cells(x, 1) Then
        
            If ThisWorkbook.Sheets("DATA").Cells(x, 2) <> "" And (InStr(blah, "|" & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|") = 0) Then
        If FirstTime = True Then
            FirstTime = False
         blah = "|" & blah & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|"
     Else
        blah = blah & ThisWorkbook.Sheets("DATA").Cells(x, 2) & "|"
    End If
End If
        'Clear cmbSub
        [B][COLOR="#FF0000"]ThisWorkbook.Sheets("CHART").cmbSub.Clear[/COLOR][/B]        
            'add to combobox
       ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
      End If
       
    Next x
      
    
    myArray = Split(blah, "|")
      
    
    For Each cell In myArray
    If cell <> "" Then
        
         ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
      End If
    
    
    Next cell
    
        
          ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1
    End Sub


any sugestions?
Click to expand...
Try moving the red highlighted line out of the loop, place it in front of the For statement so that it is not executed over and over again in each iteration of the loop.
 
Upvote 0
I had it there before and what it does there is returns all of the values based on the combobox1 not just the unique values. Then i moved it to the position that you see above and I get only 1 unique value per combobox1 selection instead of all of them. Thank you for your help. I guess I should have stated this before.
 
Upvote 0
Solved via Excel '13 VBA Cascading ComboBox - Trouble getting unique values in Combobox2 - Stack Overflow

Code: 
<code>Dim wsChart As Worksheet
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsChart = ThisWorkbook.Sheets("CHART") 
Set wsData = ThisWOrkbook.Sheets("DATA")

.....(insert rest of code here)

For x = 2 To lr
    If MyVal = wsData.Cells(x, 1) Then
       'add to combobox
        ValueToAdd = wsData.Cells(x,2) 'Get value from worksheet
        If InStr(listOfValues, wsData.Cells(x,2)) = 0 Then
        'Check to see if the value has already been added
        'If not, add to values added and add the item to the combobox.
              listOfValues = listOfValues & ValueToAdd
              Me.cmbSub.AddItem valueToAdd
        End If
    End If
Next x</code>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
My VBA is only copying the last row of data instead of all the rows
2 3
Replies
24
Views
2K
Alex Blakenburg
A
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
1K
Alex Blakenburg
A
D
  • Solved
VBA code to find all cell in Col D in sheet 1 which contains cell value without match case from range A1 to A10 in Sheet 2
Replies
3
Views
512
deba2020
D
Guna13
  • Question Question
copy and paste data from Master data based on specific sheet Cell value ( Condition based )
Replies
4
Views
735
Alex Blakenburg
A
N
  • Question Question
Excel VBA UserForm: Populating Combobox 2 based on selected value in Combobox 1
Replies
2
Views
4K
dmt32
D

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