+ Reply to Thread
Results 1 to 14 of 14

Using OptionButtons to set a RowSource for a ListBox

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Using OptionButtons to set a RowSource for a ListBox

    I have a userform that contains a listbox and 13 OptionButtons. The OptionButtons all belong to a group called "machine".
    I want to populate the listbox with row sources that can be selected depending on which optionButton has been chosen.
    I can see that a Select Case should do the trick but I am uncertain of how to code the OptionButtons.
    This is the sort of thing I have in mind

    Please Login or Register  to view this content.
    Can some one help me with this code or perhaps suggest a better way of solving the problem?
    John

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Using OptionButtons to set a RowSource for a ListBox

    Please Login or Register  to view this content.
    Avoid using rowsource to populate a listbox/combobox, but use .List instead.



  3. #3
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Using OptionButtons to set a RowSource for a ListBox

    snb
    Thanks for your help, that works fine. I realise that I am stuck on the next step of the project too.
    The List is to be produced by producing a PivotTable filtered by the value j as acheived by your code.
    The enclosed code works fine but only if all the lines I have shown are REM'd out
    Please Login or Register  to view this content.
    The REM'd lines are my failed attempt to deal with the situation where there was no relevent machine number in the pivot table. The function just ignores this situation, and retains the previous value.
    It is this I would like some more help on.
    I have attached a workbook with some data and a pivot on sheet 2 and with the linked code.
    John
    John
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Using OptionButtons to set a RowSource for a ListBox

    With the help of snb I have achieved my main aim which was to use a series of OptionButtons to select which part of a Pivot Table to use as a List for a ListBox.
    Please Login or Register  to view this content.
    Achieves this, because I give a dynamic range name to the pivot table and use this as the List.
    However the situation where there is no data for a particular machine , (No 6 in the attached sheet for instance) gives a problem. No error message happens and the function simply leaves the Filter selection where it was for the previous run.
    I would like to detect this situation and leave a MsgBox saying no data available.I have been trying various ways for a day or so , so I would appreciate some help.
    John
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    How do I fire this code?

    This is a follow up to a previous post.
    I have a UserForm populated by a Listbox and 13 option buttons.
    The listbox list property is to be selected by using the optionbuttons to activate a pivot filter using code like
    Please Login or Register  to view this content.
    A workbook is attached which I hope will make this clearer.

    I would like the Listbox to populate as the option button is selected but I can't see what should fire off the code above.
    Can anyone advise please
    John
    Attached Files Attached Files

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How do I fire this code?

    You can't change the ListBox because currently you have manually set it's RowSource.

    I'm not sure how you will populate it using a PivotTable either
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: How do I fire this code?

    RoyUK
    I was going to make the relevant part of the Pivottable into a dynamic named range. This would change every time the filter was changed by clicking one of the option buttons. This named range would be either the RowSource or the list of the Listbox
    That bit works if I filter the pivot table manually. I can also get the option buttons to select the filter criterion for the pivot filter, but only by selecting the option and then firing the code by using a command button. What I want to do is for the change of the option button itself to fire the code. There are 13 of these though and any one could be pushed.
    John

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How do I fire this code?

    In the userform try something like ths

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: How do I fire this code?

    RoyUK
    That works very well thanks very much.
    Frequently there is no data in the filter for a given optionbutton. In this situation what happens is that the unfiltered list is shown.
    What I need to happen is that the code detects that there is no data and a MsgBox is displayed saying "No Jobs for this Machine". I have tried
    Please Login or Register  to view this content.
    based on your code, but this does not work. Can you help me here?
    John

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How do I fire this code?

    j_Southern,

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread. http://www.excelforum.com/excel-prog...a-listbox.html

    Thread Closed.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Using OptionButtons to set a RowSource for a ListBox

    I am still struggling with the problem of detecting when there is no data in a filter. The latest attempt is below, but it does not work.
    Please Login or Register  to view this content.
    if you run this code no filter is applied and the pivot table just includeds all the data.
    Has anyone a suggestion as to how to handle this situation?

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Using OptionButtons to set a RowSource for a ListBox

    I can't find any optionbuttons in your files.

  13. #13
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Using OptionButtons to set a RowSource for a ListBox

    snb
    I have downloaded a more recent version.
    The piece of code I am worried about is
    Please Login or Register  to view this content.
    This was a failed attempt to deal with the situation where the pivot filter gives no data. It doesn't work, all the data is presented.
    The optionbutton solution is inelegant, but it works, immediately showing the filtered list as the RowSource of the ListBox (I used RowSource as that can be permanently set up in the properties window.)
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    08-26-2006
    Location
    -
    MS-Off Ver
    2010
    Posts
    388

    Re: Using OptionButtons to set a RowSource for a ListBox

    This keeps your current method of populating the listbox using RowSource with a dynamic named range.
    Works ok.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1