+ Reply to Thread
Results 1 to 6 of 6

Updating a pivot table with VBA from Drop down list (1st item doesn't work)

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Good evening guys,

    I ran across this post http://www.excelforum.com/excel-prog...ell-value.html

    It was just close to what I needed to do so I combined the piece that TJ had put together. This is what I've come up with.
    Please Login or Register  to view this content.
    As you can see I'm sure Sheet1!C1 as the reference. That cell is a Data validation styled list. When I select anything on the sheet it works, with the exception of the first item.

    I've tried renaming the item, I've tried hardcoding the 1st value in and still a NO GO.

    I'm getting a Run-time error 1004
    Unable to set the Visible property of the PivotItem class.


    Please advise,
    Thanks,
    tray262
    p.s. by no means am I a coder, I typically logical.

  2. #2
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Re: Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Hi Tray,

    Basically what are you trying to achieve? It seems you want to use drop-down list to update the pivot table.. but what you wish to update in the pivot table and from where you'll get that criteria or logic? Is it the value, the fields, the filter or basically the source of the pivot? The given cOdE shows me the fields, is it like, you wish to see only a fixed field among the list of fields in pivot table?

    To hide or unhide the rows, we basically use the following statements:

    Please Login or Register  to view this content.
    cOdEsLiZeR - Back after a long break.. Let's sLiZe some more cOdEs!!

  3. #3
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Re: Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Read over your post again and thought if this is what you've been trying to look for. Following is a generic code that can be applied to any pivot table to get the filtered result. It takes in a few arguments like Worksheet, Name of the Pivot Table, Field name and the conditional value.

    Please Login or Register  to view this content.
    I have used the same method in Worksheet_Change() event to trigger the macro whenever I make changes in Range("C1")
    Please Login or Register  to view this content.
    See if this meets your requirements.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Hey codeslizer,

    This is really close, I like that it's "generic". That will help when I go to adapt it for "actual" application. Ran into a snag. Once again I'll remind, I'm not a coder. But I'm typically pretty good at looking at code figuring out how to change it.

    So the snag I ran into is that my pull down box will be different sheet than my pivottable. This is how I adapted your code. Of course it doesn't work. lol.

    Am I close?
    Please Login or Register  to view this content.
    Is my logic even close. The above section basically says, If I do something in "C1" Then create a variable with the following info "PivotData","PivotTable1","Assessments", and the value in the C1 cell.

    Please Login or Register  to view this content.
    This section first takes the "chain" that we made in the other code and FilterFields, I breaks it down into small "chunks" and tells the system what each chunk is, in this example most are "strings" aka text.

    Then it makes a "new" variable called pvtField that is basically ending up as PivotData.PivotTable1.Assessments

    Then it accesses that pivottable at the above location and does the following operations. sets orientation (What is that), sets the current position to 1 (I'm assuming that 1, first position, 0 is not first position?), It clears all "values" under the assessment filter.

    Then the If statement. I'm really confused by this one...... could you explain this a little?


    I'm a want-a-be coder. lol

    Thanks,
    Tray.

  5. #5
    Registered User
    Join Date
    11-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Ok I found my one problem and ended up with another. I had it labeled "Workbooks" instead of the proper Worksheets

    So the problem. The first item in the list is still giving me an error and I'm racking my brains to figure it out.

  6. #6
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Red face Re: Updating a pivot table with VBA from Drop down list (1st item doesn't work)

    Quote Originally Posted by tray262 View Post
    I ran into is that my pull down box will be different sheet than my pivottable.
    Am I close?
    Please Login or Register  to view this content.
    Yes, you are. The implementation remains same only need to provide valid references. So if I say, let "FilterSheet" be the sheet where you have the filter and "PivotData" is the sheet where we have the PivotTable, then you'll have to first track where you'll be making the change..?? That's "Filtersheet" sheet's Range("C1") of course.. yes, so will have to write the code in this sheet's Change event. I believe that's what you've done. So, +1 point buddy!!

    Now, to adapt the changes, you'll have to keep track of two things - mainly Worksheet name and Range that we'll be providing. Before that, a brief introduction to the generic code's parameters:

    1. sht As Worksheet - This is the sheet where the Pivot table resides. So in this case, its "PivotData"
    2. PvtTABName as String - Is the name of the PivotTable. To get the name, select the Pivot table. You'll see two new tabs "Options" and "Design" has appeared at the end of the ribbon with a heading "PivotTable Tools", just click "Options". You'll find the pivot table name on the first left field.
    3. FieldName as String - Is the name of the field (basically where you wish to perform the filter). In this case is "Assessments" column. Btw, Fields are columns and rows are records.. just FYI.
    4. Finally FilterString As String - is the string based on which you want to perform the filtering. In my attached example, these were the values of "Assessments" column/field.

    Now, let see how the macro is gonna work for you. Firstly, as you'll be making changes to the sheet which is not containing the PivotTable, we'll first have to get to the sheet where we have the Pivot Table. So for that, will do..
    Please Login or Register  to view this content.
    Well, it seems I and the computer is now on the same sheet. So, now I need to direct the computer to the column on which I need to perform the operation. I know where it is, as I have its address.. so I'll create a PivotField variable to hold the location by using the following statement..
    Please Login or Register  to view this content.
    Now even computer is holding the same address, so what are we waiting for..? Lets do the operations!
    Please Login or Register  to view this content.
    1. Orientation basically defines location of the field/column. You might have seen Four sections on the Field List pane. Report filter is what we use for filtering purposes - which is what we have used in the example too. This property returns or sets the xlPivotFieldOrientation Enumeration which are xlPageField also 3 (Filter), xlRowField also 1 (Row Labels), xlDataField also 4 (Values), xlColumnField also 2 (Column Labels), and xlHidden also 0 (Not visible or checked).
    2. Alright, now let's walkthrough .Position = 1. It simply tells the computer to perform the defined check before any other. You might seen in the pivot rows section that the first item in the row gets first highlighted and becomes the root of any other nodes that comes after it.
    3. ClearAllFilters is a piece of code to make sure no filters are applied before I put any filter on the pivot fields.
    4. And now, the finale.. basically, just .CurrentPage = FilterString would do, but am making sure if no filter is provided then pivot needs to come back to normal. In other words, apply filter only if there is any condition applied, if not, make it normal.

    Hope that clears all working of my codes..
    Here's the updated code for the FilterFields
    Please Login or Register  to view this content.
    and for FilterSheet's worksheet change event..
    Please Login or Register  to view this content.
    Please note, am using Sheet3 or can use Sheets("PivotData") to run the macro. Earlier you were getting error coz it was ActiveSheet which means "FilterSheet" and there we had no Pivot table so you were getting the error. Attaching the reworked sample file for your references.

    P.S. you can use Application.ScreenUpdating = False to avoid flickering and set it back to True when done running macro. Google about it you will find it handy in use.
    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)

Similar Threads

  1. Conditional formatting applied to a pivot table doesn't work as it should
    By Pichingualas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2012, 07:43 AM
  2. VBA code for (un)hide columns related to drop down list doesn't work
    By maumon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2010, 08:27 AM
  3. [SOLVED] Pivot Table Looses Drop-Down Choice When Only One Item Displayed
    By MarvInBoise in forum Excel General
    Replies: 3
    Last Post: 05-12-2006, 01:40 PM
  4. "Show Field List" in Pivot Table Toolbar doesn't work
    By Flyer27 in forum Excel General
    Replies: 0
    Last Post: 04-11-2006, 07:10 PM
  5. [SOLVED] Selecting next drop down item in pivot table with a macro ???
    By Keilan Knight in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2005, 08:06 AM

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