Ignore/Select all option in dropdown comboboxes

anshovevis

New Member
Joined
May 17, 2017
Messages
2
Hi, I'm still a VBA novice, however I have some basic understanding of C# but can't seem to figure this one out. On this forum as well, I found this wonderful code for dropdown comboboxes, however I would like to give the user the option to either ignore one of the comboboxes or implement a select all option in each combobox. This would basically skip this combobox, and proceed to the next one with all the possible data from that previous combobox if that makes sense. I already did a lot of a trial-and-error but am unable to figure it out.

Let me make myself a little bit more clear, this is the little modified code which I found in this topic: https://www.mrexcel.com/forum/excel-questions/146973-excel-user-form-filter-combo-box-based-other-2.html

Code:
'cascading comboboxes :-)'sources in corresponding columns
  'box1 = column1 ...
'several comboboxes (see N)
  'to expand:
    'add combobox on userform
    'Const N = number of boxes
    'add Private Sub ComboBox ..N.. _Change()
    
Option Explicit


Const N = 8
Public flag As Boolean
Private r As Range, dic As Object

 
Private Sub userform_initialize()
Dim x As Variant
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet4")
    For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
        If Not IsEmpty(r) And Not dic.exists(r.Value) Then
            dic.Add r.Value, Nothing
        End If
    Next
End With
x = dic.keys
Me.ComboBox1.List = x
End Sub


Private Sub ComboBox1_Change()
update_comboboxes (1)
'general syntax
'update_comboboxes (Application.WorksheetFunction.Substitute(ActiveControl.Name, "ComboBox", ""))
End Sub
Private Sub ComboBox2_Change()
update_comboboxes (2)
End Sub
Private Sub ComboBox3_Change()
update_comboboxes (3)
End Sub


Private Sub Combobox4_Change()
update_comboboxes (4)
End Sub


Private Sub Combobox5_Change()
update_comboboxes (5)


End Sub


Private Sub Combobox6_Change()
update_comboboxes (6)


End Sub


Private Sub Combobox7_Change()
update_comboboxes (7)


End Sub


Sub update_comboboxes(nr As Integer)
Dim ws As Worksheet
Dim i As Integer
Dim check As Boolean
Dim x As Variant


Set ws = Worksheets("Sheet4")
    For i = nr + 1 To N
    Controls("ComboBox" & i).Clear
    Next i
Set dic = CreateObject("Scripting.dictionary")
    With ws
        For Each r In .Range(.Cells(2, 1), .Cells(65536, 1).End(xlUp))
            For i = 1 To nr
            check = r.Offset(0, i - 1) = Me.Controls("ComboBox" & i).Value
            If check = False Then Exit For
            Next i
            If check And Not dic.exists(r.Offset(0, nr).Value) Then
                dic.Add r.Offset(, nr).Value, Nothing
            End If
        Next
    End With
    With Me.Controls("ComboBox" & nr + 1)
        x = dic.keys
        .List = x
        If .ListCount = 1 Then .ListIndex = 0
    End With
End Sub
Now this is my very basic design, because i'm just testing it in a new workbook, so don't pay attention to the layout please



So what I would like to do now, is give the user the opportunity (in every combobox) to select a final option 'select all' - or whatever you call it - which basically goes to the next combobox with all the data. So Instead of making a selection, which redudes the options in the next combobox, go through with all the options and display them alike in the next combobox.

So let's say my data is like

Alger - EUR - CAP
Alger - USD - CAP
Schroder - USD - CAP
SCHRODER - USD - DIS
Option y - USD - DIS

If the user selects 'all' it select the data from all previous options, and displays EUR-USD & preferably another 'select all' option to the user.

I tried to make myself as clear as possible I think, and would really appreciate any help! As I said, I have some understanding of VBA code, and can basically read the whole code, but just can't seem to implement this.
 

anshovevis

New Member
Joined
May 17, 2017
Messages
2
Can't edit my post but wanted to add a few more screenshots. Replaced the comboboxes a bit more, and also added a screenshot of my data set, bc I thought it was a bit unclear now.





So let's say for example, if the user selects the 'all' option I want to implement, it should display all the currency's in the 2nd combobox, based on the previous one: USD, EUR HDG, EUR & another 'all'. But let's say they first select Schorder and then select 'all' in the 2nd combobox, it should only display the values which are equal to combobox 1 = true & combobox 2 = all options if combobox 1 is true eg row 2, 3, 4 in that case.

Ok, that's about it. Please let me know if you have any more questions, and I look forward to your ideas.
 

Forum statistics

Threads
1,085,496
Messages
5,384,000
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top