+ Reply to Thread
Results 1 to 7 of 7

Listbox for pivot table selection

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,703

    Listbox for pivot table selection

    I have a macro that selects specific items in the various pagefields of a pivot table, but one pagefield is throwing me, because I don't have a "Specific" item to select.

    The overall purpose is to create a forecast, and to do that we have to select some of our history, but not all. Therefore, when it comes to this particular pagefield, the user needs to select from 3 to 12 different months to use, depending on the user's need.

    I would like to use a listbox, and have it populate from the Pagefield, so the user can select the dates to use from that, but I don't know how to set it up. I've looked on the internet, and found a lot of hits but no help. I'd sure appreciate some help from you.

    Thanks,
    John

  2. #2
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Listbox for pivot table selection

    Please use the following code. It will add the items. Let me know if you nee something else too.

    Thanks,
    Vikas

    Assumptions : Sheet name if Sheet2, and pivot table name is PivotTable 1

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,703

    Re: Listbox for pivot table selection

    Vikas,

    I'm getting a "Variable not Defined" on ComboBox1. Do I need to first create a blank comboBox?

  4. #4
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Listbox for pivot table selection

    Oh yea, Combobox1 is the control which I am loading the pagefields.

    Vikas

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,703

    Re: Listbox for pivot table selection

    Vikas,

    I don't understand your answer. Let me see if I can make my situation clearer:
    User opens "Staffing Projection Tool.xls", which opens "Paid FTEs.xls", which contains the pivot table in question. Userform comes up, asks user which Region they want. User selects, for instance, region 030. Macro selects region 3 in pivot table, makes some other modifications to pivot, then it needs to know what dates to use. I don't want my user interacting with the spreadsheet, but the user needs to select the dates to use. That was why I asked for a listbox. If we can do it with a combobox that's just fine, I just don't understand how. Below is the code I'm trying to use.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Listbox for pivot table selection

    Hi there!

    It is upto you whether you want to use Listbox or Combobox. You can use and chose the data in any of the control of your choice. Listbox will allow user to select multiple items, but combobox gives option to select only one. So this is entirely your choice.

    If you use Combobox, then you will need to load the values in Combobox when you initialize the form so that user can select the date before you click the button to generate report.

    I will do something like following code to ensure that the date is selected in pivot table. If it doesn't solve your query, then please send the spreadsheet either by uploading here, or if it is heavy then you can send it @ vikasbhandari2@gmail.com.

    Please Login or Register  to view this content.
    Waiting for your response!!!

    Thanks,
    Vikas

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit
    Posts
    3,703

    Re: Listbox for pivot table selection

    Vikas,

    I've attached a sample, but had to WAY strip down the data file, so I don't guarantee it will work flawlessly. The way it's supposed to work, open the Staffing Projection Tool. Then it normally will open the PAID_FTES_BR20102.xls (also in the zip file), but since it won't know the file path you might have to help with that. Once it gets PAID FTEs open, it will make adjustments to the pivot table. It will throw up a dialogue to choose the region (choose region 3; that's the only data I shipped). Then I want another dialogue where the user can select the dates ( multiple dates, from 3 to 15 dates) to be used in the forecaster. That's where it's breaking down. Any help you can provide is greatly appreciated. I started, based on the code you provided yesterday, to build another userform, but it's not completed.
    Attached Files Attached Files

+ 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