Search combobox value on a pivot table and return data to display on a listbox

  • Hello everyone

    This is my first post on this forum, and i would like to get some help from you

    Im making a program on vba in order to control inventory, inputs and outputs, im a very beginner on VBA programming and i have a lot of doubts about the syntaxis and other stuff, but the main concern is that i would like to display specific values from a pivot table in a listbox when the user choose a combobox option, the pivot table is dynamic so the data will be changing (only rows).

    The current code that im using to show data in the listbox from the pivot table (all data) is the following one (i copied from internet) *Works to show all data from the pivot table on the listbox*


    Dim pvtTable As PivotTable

    Dim vData As Variant

    Set pvtTable = Worksheets("Data").PivotTables(1)

    With pvtTable.TableRange1

    '--read data into array

    vData = .Offset(1).Resize(.Rows.Count + pvtTable.ColumnGrand - 1)

    End With

    With Me.lbxPedidos

    .ColumnCount = UBound(vData, 2)

    '--transfer array values into listbox

    .List = vData

    End With


    So, my question is: is there a way to show only specific values from the pivot table on the listbox? For example:

    This is the pivot table (only rows fields)

    Im showing the same data on the listbox, what i want is to show only the data that belongs to the color that is choosen by the user, something like this:

    If the user choose red on the combobox of the userform

    Ive tried to be as clear as posible i hope that someone could help me!

  • Hello and Welcome to the Forum :)

    From your description, it looks like the User is selecting somewhere a color (e.g. Red )

    So your could have your Pivot Table refreshed with this selection... in order to have the exact output you need to display ...

    Does it make sense ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Carim, thanks for your quick answer

    Yes, the main userform has a combo box that allows the user to select between the options (red , green or blue), the program refresh the pivottable when the userform is initialized so the data is always updated, the problem is that the listbox is always showing all the data of the pivot table, and i want to show only the data of the color that is selected for the user in the userform, so how can i search with vba properties in the pivot table the value selected from the user on the combobox and after show only the data that belongs to the color on the list box? For example of the user select Green the program search on the first column and when it finds it, it will show on the list box the next info

    Hope it will be more clear :(

    Thank you!

  • Sorry i wrote my answer on the post haha :(

  • Hello again,

    You are saying : whenever the User selects a color ...the Pivot Table is refreshed and as a result ONLY displays the selected color ...

    But the ListBox does not updated as it should ...

    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again Carim

    Maybe i wasnt clear enough, let me try to explain you better

    First of all i have a pivot table that is made from a database automatically, so if the user edit the database, the dynamic pivot table refresh everytime, (thats not the issue), so this is my dynamic pivot table on a worksheet

    As you can see i have 3 different pen colors (Red, blue and Green), also i have the options for the pens type and a bags qty that contains a number of pens that i have on my inventory, Example (row 2) : It means that i have 83 units in 1 bag of the option A of Red pens right? thats only the dynamic pivot table

    Now, i have the next userform for the requests

    When a client wants an article, the user has to insert the request data in the requests userform, the current situation is that the listbox shows all the data from the dynamic pivot table even if the user choose an option by the combobox (color), what i want is when the user choose an option with the combobox (red, blue or green) the listbox will show only the data of the option selected wich means that if the user choose on the combobox the red option, the listbox has to show only the next info (marked with blue marker)

    Sorry for my drawings but do you know how can i show only the data of the option selected? Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!