Consulting

Results 1 to 10 of 10

Thread: Dependant ActiveX comboboxes filtering multiple pivot tables Issue

  1. #1

    Dependant ActiveX comboboxes filtering multiple pivot tables Issue

    Hello everyone,
    I am using Office 365 and I made a dashboard which is built from several pivot tables and is filtered by some slicers and three dependant ActiveX comboboxes. The first combobox should filter the "Product" field, the second the "W.O." field and the third the "Operations" one.

    Currently I almost managed to complete the first combobox, but I still have some problems due to my limited knowledge. I would like to learn and would really appreciate your help!

    My current goals are as follows:
    1. make the macro I wrote more stable (I am a beginner with VBA and every tip is always welcome!), because sometimes, if I delete the names of the products filtered in the combobox itself, they might even disappear;
    2. being able to clear the comboboxes and make the pivot tables return as before;
    3. filter the "Product" and "Progress" tables, located in their respective sheets (it seems like I am not able to filter tables which do not have the "Product" field in the pivot filter...);
    4. connect (if possible, because it would be a gem!) the slicers to the comboboxes, so that they react whenever they filter a product.



    Down below you have my code and my attached sample file.

    
    
    Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    Sheets("DB_Joined").Visible = True
    Application.ScreenUpdating = False
    Sheets("Widgets").Select
    ActiveSheet.PivotTables("Widgets").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Cronology").Select
    ActiveSheet.PivotTables("Cronology").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Department").Select
    ActiveSheet.PivotTables("Department").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Managers").Select
    ActiveSheet.PivotTables("Managers").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Engineers").Select
    ActiveSheet.PivotTables("Engineers").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Notes").Select
    ActiveSheet.PivotTables("Notes").PivotFields("Product").CurrentPage = Me.ComboBox1.Value
    Sheets("Combo").Select
    End Sub


    Thank you very much in advance!
    Attached Files Attached Files

  2. #2
    Hello everyone,
    I hope I am not too late to apologize. Unluckily yesterday I couldn't find the forum rules and couldn't think about explaining that I already opened a similar thread here:
    Help with three dependent ActiveX comboboxes? (excelforum.com)

    I did not mean to lack of respect to anyone. The fact is that I am not that expert in using forums and the need to receive help made me act without realizing I was actually breaking a rule.

    I am sorry and I am willing to do whatever it takes to fix my mistake.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Just to clean up your Macro. I haven't looked at your upload yet.

    Option Explicit
    
    Dim ProductPivotSheets As Variant
    
    Sub RunOnce()
    'This sub Initializes all global variables.
    'run this before any other subs
        ProductPivotSheets = Array("Widgets", "Cronology", "Department", "Managers", "Engineers", "Notes")
    End Sub
    
    Private Sub ComboBox1_Change()
    Dim CPName As String
    Dim i As Long
    CPName = Me.ComboBox1.Value
    
    Application.ScreenUpdating = False
        For i = LBound(ProductPivotSheets) To UBound(ProductPivotSheets)
            Sheets(ProductPivotSheets(i)).PivotTables(ProductPivotSheets(i)).PivotFields("Product").CurrentPage = CPName
        Next i
        
        Sheets("DB_Joined").Visible = True
    Application.ScreenUpdating = True '<------------ Important: Screen Updating is persistant
    
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Hello SamT,
    first of all thank you so much for understanding and helping me.

    I know that you haven't looked at my file yet, but out of curiosity I read and tried your macro... and I must admit that what you did amazed me. Not only your macro "cleaned" the mine, but also taught me something I wouldn't have been able to achieve just by myself!
    I would be pleased if you found the time to check my file, too, because surely I could learn something more from experts like you!

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Jinpachi M. View Post
    Hello everyone,
    I hope I am not too late to apologize. Unluckily yesterday I couldn't find the forum rules and couldn't think about explaining that I already opened a similar thread here:
    Help with three dependent ActiveX comboboxes? (excelforum.com)

    I did not mean to lack of respect to anyone. The fact is that I am not that expert in using forums and the need to receive help made me act without realizing I was actually breaking a rule.

    I am sorry and I am willing to do whatever it takes to fix my mistake.

    No problem

    Don't worry about it

    We're here to help, we just like to know if you already have an answer in another forum (although everyone knows that VBAexpress is the best )
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Hello Paul,
    you are right: I actually received an answer from someone who kindly advised me to use slicers instead of comboboxes. It is a good solution, because it actually shows what I would like to accomplish, but it causes me a couple of problems:

    - 1: in reality I have to manage thousands of products and honestly I think that having a huge slicer with so many buttons risks to be too much dispersive...;
    - 2: I decided to give the possibility to open PDF files associated with the results of the filtered products info obtained and, if I am not mistaken, it is not possible to obtain such a function with slicers (or their own filters).

    In my opinion dependant comboboxes still look like what I need, but if you have an alternative solution I'm open to anything. ��

    Thank you for letting me clarify!

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Personally, I'd still go with slicers

    Adding a helper 'Starts With' column makes it easier

    Play with the attachment. It could use a lot of polishing, but show the idea

    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Hello Paul,
    thank you, it's an interesting solution and I like it. I will definitely try to learn it for myself, but another problem of mine is the fact that I will have to share this work with other people, who would surely not find it so intuitive to use. I would stick to a more "classical" design to facilitate the acceptance of this work.

    Out of curiosity, may I ask you if there's a way to open a PDF file with the results of filtered slicers?

  9. #9
    Hello everyone,
    I have the doubt that my last post might be misunderstanding, so I would just like to clarify that by "classical design" I meant "keeping the comboboxes": they're definitely harder to make, but easier to use and understand, especially for someone who isn't so used to computers.

    About the macro for opening PDF files, if it's a problem don't bother making it: I didn't actually ask it in the first place, because before asking help here I would like to try to write a new code on my own (hoping I will manage to do it!).

    As always, thank you for your kindness!

  10. #10
    Hello everyone,
    I'm sorry to bump this, but I would almost like to know if it is possible to filter the "Product" and "Process" tables together with all the others.

    I would really like to finish this work, but I've been stuck for months and I confess I'm starting to feel discouraged.

Posting Permissions

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