Results 1 to 10 of 10

Thread: How to updated pivot tables on different tabs from user form parameters?

  1. #1

    How to updated pivot tables on different tabs from user form parameters?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I'm brand new to vba only a few weeks actually. So here is my scenario. This is a macro-enabled workbook for the Board of Education. The workbook is 31MB and is at box.com. I can send the link. Click on View on the top and then look to the right on the ribbon. Click on the Parameter Selection icon and you will see the attached screenshot.

    This allows the user to select the entity to examine. If they do not select an ISD in combobox1, it should default to everything. If they select an ISD like Oakland Schools above, but nothing in Combobox2 than all School Districts will be included and so on. Once they have made their selection, they click OK. Now, what hopefully should happen is the data for their selection should be pulled from the “Combined” tab and put in the Pivot Tables on the Working Pivot Tables tab. Once that is done, another function should take the data from these Pivot tables and “refresh” the graphs and charts on the Summary tab at the front of the workbook. I hate Pivot tables as I've never really "got" them so to speak.

    One thing I’m still trying to correct is the data that populates this workbook comes from a web site that must be downloaded manually (don’t ask). It comes down as csv. You can see from the image above the “code” columns should be 5 characters with leading zeroes. Unfortunately, the source data is not that way. I have saved the source file as xlsm, formatted these code columns as custom since text won’t work and imported them into the workbook. The data moved over to the Pivot Tables (manually done to this point) does not maintain the leading zeroes.

    Any help would be greatly appreciated as I'm in over my head clearly.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Parameter Selection.jpg 
Views:	39 
Size:	91.7 KB 
ID:	2524  

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Dovermac. How about using slicers instead of a User form to connect all pivots. User clicks on the slicer, and Excel magically filters all the pivots in the book to match what they clicked. Seamless. As an aside, I've written some posts recently about determining which slicer they clicked on, in the event that you want to do something else. See:http://dailydoseofexcel.com/archives...te-episode-iv/

    Also, don't fear the pivot! They make a developer's life soo much easier. Subscribe to Deb Dalgleish's site 'Contextures' blog for heaps of great tutorials and scan her downloads page for some awesome code re pivots.

  3. #3
    Hi Jeff,
    I don't even know what a slicer is. That's how green I am. The project leader dictated these specs based on the requests of the stakeholders. That's what I was told.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Quote Originally Posted by dovermac View Post
    The project leader dictated these specs based on the requests of the stakeholders.
    Ok. What excel version will users be using? Can you send me a link to the file? I'm at weir dot jeff @ gmail dot com.

  5. #5
    It's Excel 2010. It was on box.com, but my account expired. It's 31MB. Do you have a suggestion as to an alternate way to get you the file?

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Strip out most of the data, and email it? Or put it in a DropBox account and send me the link?

  7. #7
    I got it down to 12mb, I hope it's enough for you to decipher what's going on. Not sure hotmail will allow that much data?!?

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay. I'm off to bed now. Might not get a chance to look at this for upwards of a day, as busy day at work tomorrow. But if it comes through, I'll take a peek and see if I can help.

  9. #9
    I setup a dropbox account and will put the complete file up shortly.

  10. #10
    Ok, the complete workbook and screenshot of the PS form are at https://www.dropbox.com/s/dmrhr24naa...Dashboard.xlsm Let me know if you are able to get to the file(s).

Posting Permissions

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