The World’s Largest Online Community for Developers

'; vba - How to trigger event in one ComboBox when value selected in another ComboBox? - LavOzs.Com

I have a ComboBox "select the view" which contains (WEEK, MONTH, QUARTER and ALL). I would like when the first ComboBox is selected for example : "WEEK", the second ComboBox "select the period" will be populated without a click button with "W1,W2,....W52" or if we select "QUARTER" will be populated with "Q1,Q2,Q3,Q4" etc.

Form View

I tried the code below but it didn't work quite well.

Private Sub ComboBox4_Change()

Dim index As Integer

With Sheets("SUMMARY")
index = .ComboBox3.ListIndex

    With .ComboBox4
        Select Case index
            Case Is = 0
                .AddItem "W1"
                .AddItem "W2"
                .AddItem "W3"
                .AddItem "W4"
                .AddItem "W5"
                .AddItem "W6"
                .AddItem "W7"
                .AddItem "W8"
                .AddItem "W9"
                .AddItem "W10"
                .AddItem "W11"
                .AddItem "W12"
                .AddItem "W13"
                .AddItem "W14"
                .AddItem "W15"
                .AddItem "W16"
                .AddItem "W17"
                .AddItem "W18"
                .AddItem "W19"
                .AddItem "W20"
                .AddItem "W21"
                .AddItem "W22"
                .AddItem "W23"
                .AddItem "W24"
                .AddItem "W25"
                .AddItem "W26"
                .AddItem "W27"
                .AddItem "W28"
                .AddItem "W29"
                .AddItem "W30"
                .AddItem "W31"
                .AddItem "W32"
                .AddItem "W33"
                .AddItem "W34"
                .AddItem "W35"
                .AddItem "W36"
                .AddItem "W37"
                .AddItem "W38"
                .AddItem "W39"
                .AddItem "W40"
                .AddItem "W41"
                .AddItem "W42"
                .AddItem "W43"
                .AddItem "W44"
                .AddItem "W45"
                .AddItem "W46"
                .AddItem "W47"
                .AddItem "W48"
                .AddItem "W49"
                .AddItem "W50"
                .AddItem "W51"
                .AddItem "W52"

            Case Is = 1
                .AddItem "JANUARY"
                .AddItem "FEBRUARY"
                .AddItem "MARCH"
                .AddItem "APRIL"
                .AddItem "MAY"
                .AddItem "JUNE"
                .AddItem "JULY"
                .AddItem "AUGUST"
                .AddItem "SEPTEMBER"
                .AddItem "OCTOBER"
                .AddItem "NOVEMBER"
                .AddItem "DECEMBER"

            Case Is = 2
                .AddItem "Q1"
                .AddItem "Q2"
                .AddItem "Q3"
                .AddItem "Q4"
            Case Is = 3
                .AddItem "ALL"
        End Select
    End With

End With

End Sub
  1. I'd break it up into two functions, easier to maintain.
  2. you can use arrays to make code smaller
  3. My code assumes ComboBox3 contains the filter and combo4 is what needs to be populated. I think you may have mixed those two up above.
Private Sub ComboBox3_Change()

    with thisworkbook.worksheets("Summary")
        call PopulateCombo4(.ComboBox3.value)
    end with

end sub
Private Sub PopulateCombo4(val as string)


    dim arr() as string
    if val = "QUARTERLY" then
        arr = split("Q1,Q2,Q3,Q4")
    Elseif val = "WEEKLY" then
        '....[same as above with W1,W2....]
    end if            

    dim s as string
    for each s in arr
        thisworkbook.worksheets("Summary").combobox4.additem s

end sub

Let me know if you have any follow up Qs. going to meeting.

How to find event listeners on a DOM node when debugging or from the JavaScript code?
Android Spinner: Get the selected item change event
How to trigger the window resize event in JavaScript?
How do I style a <select> dropdown with only CSS?
How to trigger event in JavaScript?
jQuery multiple events to trigger the same function
How to avoid using Select in Excel VBA
Inserting a column conditionally based on date using VBA
How to extract data from a concatenated string in excel macro