L
Legacy 3234
Guest
Sorry if the title is unclear. I'm really not sure what direction to take with this. Here's the situation: I have an Excel 2010 workbook with several pivot tables running off of an Oracle table of over 6 million records. There's a "main" pivot table where users will look at actual data. There will be smaller pivot tables containing chunks of the same data that will be hidden behind pivot charts. (Correct me if I'm wrong but I can only build one pivot chart per pivot table, right? That's the reason for so many pivot tables.) And my co-worker says it's best to have the charts on the same sheets as their pivot tables or problems can happen.
There are nine pivot fields where selections are made. One of these will be a switch that is tripped by a selection made in another pivot field. The pivot tables for the charts will be driven by the "main" table. All this I can do (and have done) with combo-boxes to drive the pivot table selections and a button to make the tables update. It was all working very well and the pivot table updates were pretty fast, considering the number of records being handled.
I tried out that code on "Contextures" that causes all pivot tables in a workbook to update when one is changed but it's pretty slow, especially if you have to wait for it to run every time you change a pivot field and you need to change 4 or 5 of them.
The problem with combo-boxes is that the boss man wants to be able to make multiple selections for certain fields and you can't make multiple selections in a combo-box.
I see that multiple selections can be made in a form list box (which I've never actually used) but that's kind of ugly and users have to hold down the CTRL key to select multiples and I'd prefer they didn't have to do that. So I was thinking I could make a user form (which I've never done before, either) with check boxes (new to me, too) for each pivot field item that could be brought up from a button click (or something.) After selections are made, another button click would drive the pivot tables to update. If a form is a bad idea for this I suppose a splash screen for the selections would work well enough.
Since I know so little about this area I don't want to go down this path without some advice? Does the form plan make sense? If not, what would be the best way to accomplish this? If a form is a good idea, can someone recommend some resources on how to build this kind of form (all I'm finding is how to build data entry type forms), and how to connect the code to it?
Thanks for reading all that!
There are nine pivot fields where selections are made. One of these will be a switch that is tripped by a selection made in another pivot field. The pivot tables for the charts will be driven by the "main" table. All this I can do (and have done) with combo-boxes to drive the pivot table selections and a button to make the tables update. It was all working very well and the pivot table updates were pretty fast, considering the number of records being handled.
I tried out that code on "Contextures" that causes all pivot tables in a workbook to update when one is changed but it's pretty slow, especially if you have to wait for it to run every time you change a pivot field and you need to change 4 or 5 of them.
The problem with combo-boxes is that the boss man wants to be able to make multiple selections for certain fields and you can't make multiple selections in a combo-box.
I see that multiple selections can be made in a form list box (which I've never actually used) but that's kind of ugly and users have to hold down the CTRL key to select multiples and I'd prefer they didn't have to do that. So I was thinking I could make a user form (which I've never done before, either) with check boxes (new to me, too) for each pivot field item that could be brought up from a button click (or something.) After selections are made, another button click would drive the pivot tables to update. If a form is a bad idea for this I suppose a splash screen for the selections would work well enough.
Since I know so little about this area I don't want to go down this path without some advice? Does the form plan make sense? If not, what would be the best way to accomplish this? If a form is a good idea, can someone recommend some resources on how to build this kind of form (all I'm finding is how to build data entry type forms), and how to connect the code to it?
Thanks for reading all that!
