+ Reply to Thread
Results 1 to 12 of 12

applying select case function to selectable options in listbox

  1. 12-19-2013, 02:32 PM #1
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    applying select case function to selectable options in listbox

    I have a listbox with multiple options to choose from on sheet1. On sheet2 I have the actual options that come up when selecting from the list box. the list box works in conjunction with the combobox to generate graphs. Certain options in the combobox only relate to particular options in the listbox. How do I write a select case function that will basically say, if you choose this one from the combobox, then only these options are available in the list box, and if you choose this other one in the combobox then only these other options are available in the listbox, and so on?

    Here is the code for the combobox in question: 
    Please Login or Register  to view this content.
    And here is the code for the listbox in question: 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 12-19-2013, 02:44 PM #2
    xlbiznes
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: applying select case function to selectable options in listbox

    Basically, based on the selection of the value in the combobox , you need to populate the listbox with the options.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *
    Reply With QuoteRegister To Reply
  3. 12-19-2013, 03:19 PM #3
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: applying select case function to selectable options in listbox

    Ok, that makes sense. How would the select case structure look. More specifically, how do I write the line that tells excel to populate the listbox based on whats selected in the combobox? 
    Please Login or Register  to view this content.
    Something like that?
    Last edited by Leith Ross; 01-28-2014 at 08:49 PM. Reason: Changed per OP's request
    Reply With QuoteRegister To Reply
  4. 12-19-2013, 03:42 PM #4
    xlbiznes
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: applying select case function to selectable options in listbox

    Here is a file to see how it will work and the code is in sheet1.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 12-19-2013, 03:56 PM #5
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: applying select case function to selectable options in listbox

    Awesome. Thanks so much, this is really helpful!
    Reply With QuoteRegister To Reply
  6. 12-19-2013, 04:07 PM #6
    xlbiznes
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: applying select case function to selectable options in listbox

    you are welcome.
    Reply With QuoteRegister To Reply
  7. 12-20-2013, 07:01 PM #7
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: applying select case function to selectable options in listbox

    @ xlbiznes

    I applied the code you showed me to my situation, and when I do it gives me an error message:

    run time error 424
    object required

    When I debug, it highlights the line of code that says:

    ListBox1.Clear


    Here is the code you showed me retooled to my needs. Please let me know what you think might be causeing this error, as it's basically the exact same thing you showed me and your version ran perfect. 

    Please Login or Register  to view this content.
    I am using excel 2007, not 2010.
    Last edited by Leith Ross; 01-28-2014 at 08:52 PM. Reason: Changed per OP's request
    Reply With QuoteRegister To Reply
  8. 12-20-2013, 07:14 PM #8
    Everstrivin
    Everstrivin is offline
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 365
    Posts
    236

    Re: applying select case function to selectable options in listbox

    Try 
    Please Login or Register  to view this content.
    or 
    Please Login or Register  to view this content.
    thanks to http://www.mrexcel.com/forum/excel-q...box-items.html
    If you can't figure out how a formula works, try stepping through it using "Evaluate Formula" in the Formula Auditing menu item in the tools menu!

    If you want to see where your code went wrong, try stepping through it by clicking in the code and pressing F8 and watch as the magic happens!


    If you are happy with any of the results, please add to the contributor's reputation by clicking the star icon.
    Reply With QuoteRegister To Reply
  9. 12-20-2013, 07:22 PM #9
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: applying select case function to selectable options in listbox

    @Everstrivin

    Thanks a bunch for your recommendation. One question though. You suggested "listBox1.Items.Clear();". Is the ";" part of the line of code as well?

    Also, thanks for suggesting how I can check code!
    Reply With QuoteRegister To Reply
  10. 12-20-2013, 07:31 PM #10
    Kwasimitsu
    Kwasimitsu is offline
    Registered User
    Join Date
    12-11-2013
    Location
    Emeryville, USA
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: applying select case function to selectable options in listbox

    OK, I tried it with and without, and still getting the same error of "object required". So frustrating. I dont understand what it is about "ListBox1.clear" thats tripping up the program, or what "object" that it requires. Anything helps. Thanks
    Reply With QuoteRegister To Reply
  11. 12-20-2013, 11:40 PM #11
    Everstrivin
    Everstrivin is offline
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 365
    Posts
    236

    Re: applying select case function to selectable options in listbox

    Yeah, that first suggestion was just a result from a search (not even excel vba), sorry about that one.

    For some reason the code can't find ListBox1. It might be that you need to put Sheets("Sheet1"). or userform1. before the statement.
    Reply With QuoteRegister To Reply
  12. 12-21-2013, 02:09 AM #12
    xlbiznes
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: applying select case function to selectable options in listbox

    Hi,

    As @Everstrivin, has suggested try adding the form name/sheet in front of the listbox.

    In my file both the controls , were on the sheet.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ListBox to trigger macro using Select Case
    By 4am in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-10-2011, 04:48 PM
  2. Problems with a case...select function
    By abbysdad in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2009, 04:25 PM
  3. Populate a cells selectable options based on a list
    By redbaron1616 in forum Excel General
    Replies: 1
    Last Post: 02-06-2008, 05:05 PM
  4. Calling a function from Select case
    By Blondegirl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2006, 07:13 AM
  5. [SOLVED] Select Case Options
    By Jody in forum Excel General
    Replies: 1
    Last Post: 07-27-2005, 07:05 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1