• Breaking News

    How To Create Dependable Combo Box

    Create Dependable Combo Box in userform with the help of Dynamic Name Manager.

    1.     Create a list with all the required data

    2.     The Example is here:-

    Column A is ‘All Regions

    Cell A2 name is Americas, So Column B has all the countries’ names which is in the Americas region.

    Cell A3 name is Asia Pacific, So Column C has all the countries’ names which are in Asia Pacific region.

    Cell A4 name is EMEA, So Column D has all the countries’ names which are in the EMEA region.

    Logic: So when we run the userform, combo box 1 will show all regions’ name and if Americas is selected then combo box 2 will filter and display data from Column B and so on with another region

    Now How To Create A Dynamic Name Range

    In the Ribbon select FORMULAS tab

    Now first give the range name to Column A which is All-Region. Select the range that you want to define the name. Now in Name Manager select Create from Selection. Or you can use the shortcut key Ctrl+shift+F3. Choose Top Row to make the column’s first name as Header.



    Check if the Name Manager has done proper selection or not

    Click Name Manager and you will able to see the name as well as the range that it has taken



    Follow the above steps to create the name for Column B (Americas), Column C (Asia Pacific) & Column D (EMEA)

    Note: The Name manager will automatic take underscore (_) if there is space between 2 words like   Asia Pacific.

    See below:-

    Now press alt+F11 to start VBA. Insert userform and design it.

    ¨    Take 2 Label and name as Region & Country.

    ¨    Take 2 combo box 


    Now double click on userform to initialize it. Why? So that once the userform will be displayed, the combo box will automatically pick the defined name. Here Combo box 1 we are setting the column A name range i.e. All_Region

    Private Sub UserForm_Initialize()

    Me.ComboBox1.RowSource = "All_Region"

    End Sub



    Next, we need to write code for Combo box 1. Go back to userform and double click on the combo box 1 and write the following code. Logic: When there will be a selection from

     

    Private Sub ComboBox1_Change()

    Me.ComboBox2 = ""

    Select CaseMe.ComboBox1

        Case"Americas"                                                       ‘Cell A2 name is Americas

            Me.ComboBox2.RowSource = "Americas"      ‘ The rowsource is nothing but the name range.

        Case"Asia Pacific"

            Me.ComboBox2.RowSource = "Asia_Pacific"

        Case"EMEA"

            Me.ComboBox2.RowSource = "EMEA"       

    End Select   

    End Sub

     


    Now Run userform. Select Any region name from combo box 1 and go to combo box 2 to select the country.




     

    No comments

    View My Stats