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,082,167
Messages
5,363,532
Members
400,747
Latest member
monty_gl

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top