Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    152
    Thanks
    69
    Thanked 0 Times in 0 Posts

    Selecting data from a worksheet using a combo box

    Hi Again Experts,

    I've searched google and youtube but haven't found what I need. I would like to use vba to code a combo box to select data from a worksheet and show it on a form.

    The worksheet would contain a 1000 or more rows of data divided into 15 subjects listed in column 1. For example, say that the subjects are divided by A,B,C to O (15 letter into the alphabet).


    Using the combo box on a userform I would select subject D, of the 15 subjects. Then I will be able to only see D subjects as I clicked the next button on the user form to view the contents of the row.

    I hope this explanation is adequate.

    Thanks, Experts

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    I can think of two designs that would accomplish this.

    Setup a Sheet for each Subject and only have questions for that subject on that sheet it then becomes easy to loop through the sheet once the subject is selected.

    If you don't like the multiple sheet approach you could have the selection of the subject kick off an Advanced Query that would copy only the desired questions to a single re-useable sheet that could then be easily looped through.

    As always a sample sheet to work with would be useful.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-01-29)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Excel,

    Try this code in your form to filter the sheet based on the value of the selection of combobox1.

    Code:
    Private Sub ComboBox1_Change()
        ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-01-29)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    If the selection in the combobox is just part of the contents of what is in the cells in column A then you can use:

    Code:
    Private Sub ComboBox1_Change()
        ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="*" & ComboBox1.Value & "*"
    End Sub
    For example: if you select the value "comprehensive" from the combo box, the sheet will be filtered in column A whose values may include:

    Comprehensive Evaluation
    Comprehensive
    Analytic Comprehensiveness

    HTH,
    Maud

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-01-29)

  8. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    152
    Thanks
    69
    Thanked 0 Times in 0 Posts
    RG and Maud,

    Everything is working as I had hoped, due to your kindness and expertise. Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •