VBA PivotTables and coding

Question

I have been facing a problem lately and any help to solve is much appreciated.

My sheet “Sheet1” has a PivotTable “Dyn40”. One of the PivotFields is “Options” and one of the possible PivotItems is “Potato”. I am having error #error 438: object doesn't support this property or method

Sub FindPriority()

 Dim pass As String 
 pass = "user"
   With Worksheets("Sheet1")
    .Activate
    .Unprotect Password:=pass
    Range("TK2").Select ‘ < --- is this necessary ?
    ActiveSheet.PivotTables("Dyn40").PivotCache.Refresh
      If ActiveSheet.PivotTables("Dyn40").PivotFields("Options").ListCount = 1 Then ‘check if there is at least one item in the field 
      If ActiveSheet.PivotTables("Dyn40").PivotFields("Options").PivotItems("Potato").count = 1 Then ‘check if there is at least one single item named “Potato”
         CreateObject("WScript.Shell").Popup "Only one item exists and its name is Potato" ‘ if there exists, then message pops up
      End If
      Else
      If ActiveSheet.PivotTables("Dyn40").PivotFields("Options").ListCount > 1 Then ‘check if there is more than one item in the field
           If ActiveSheet.PivotTables("Dyn40").PivotFields("Options").PivotItems("Potato").count = 1 Then ‘check if there is at least one single item named “Potato”
              CreateObject("WScript.Shell").Popup "There is more than one item and one of them is Potato"‘ if there exists, then message pops up
              ActiveSheet.PivotTables("Dyn40").PivotFields("Options").PivotItems("Potato").Visible = False ‘ and then the name “Potato” is hidden 
           End If
           Else
              CreateObject("WScript.Shell").Popup "There is nothing in here" 'if there are no fields available, message pops up
      End If
      End If

   End With
 End Sub

The next question is … if I want to replace Potato by “ “ (empty field) how will this snippet look like?

As a side note, please replace the weird `CreateObject("WScript.Shell").Popup` with `MsgBox`. The answer to `< --- is this necessary ?` is [no](http://stackoverflow.com/q/10714251/11683). — GSerg, Aug 14 '16 at 19:26
You're not saying where the error is, so I assume it's `PivotField` that does not have `ListCount`. — GSerg, Aug 14 '16 at 19:31
As a continuation to @GSerg's comment, does `RecordCount` work there? — Doug Glancy, Aug 14 '16 at 20:58
@GSerg. Hi. In fact, ListCount is not part of PivotField and the error is at the line `ListCount = 1 Then`. In fact the PivotField items are: Application; Count; Creator; Item; Parent — Luiz Vaughan, Aug 14 '16 at 21:12
What I am trying to avoid is to force the invisibility of the item "potato" in the fields option via VBA without making sure that “potato” will be there once the pivot table is refreshed. Otherwise, if there is nothing there to hide, the user can get an error. By counting the pivot items first and guaranteeing that at least two entries are available and, most importantly, one of them is indeed the "potato" option, I could then conclude the operation successfully. Can anybody help? — Luiz Vaughan, Aug 15 '16 at 16:54
@GSerg. Why using `"WScript.Shell"` is worse than `MsgBox`? Memory issues? — Luiz Vaughan, Aug 15 '16 at 20:53
`What I am trying to` - it does not really matter what you are trying to do, it only matters that you trying to use methods that do not exist. Do not do that. Use methods that exist instead. If you want to count pivot items, count them with `PivotItems.Count`. `Memory issues?` - no, it's just weird. I mean, it sure has way more overhead than `MsgBox`, but the core problem is that you are using an external COM object for a function already built into the language. That would be like calling into a `Declare`d function to sum two numbers instead of using the `+`. — GSerg, Aug 15 '16 at 21:22

Luiz Vaughan · Accepted Answer · 2016-08-20T17:57:37.957

I figured out a new method of doing the same thing without the use of VBA. Save your brain to do more important coding. Here it goes the solution to the above problem:

As in the beggining, the Filters field (People and Options) were constant data, meaning that no new People nor Options would be considered as new entries, since they were fixed items. The Row Labels and Values fields were, on the other hand, variables, meaning that their content could be altered, added or removed within the data base as wished - picture 1.

Supose now that I want to filter one person "Laetitia" from the field People, excluding her from my results. By knowing beforehand that all new entries regarding People will not change (no new names will be added to the list for this specific example), once the pivot table is refreshed with new data, the integrity of our results will be good - Picture 2

Supose now that I want to filter one vegetable "Potato", excluding it from our results in the field Vegetables. Potato is in the Row Lables and this field is variable, meaning that new vegetables and new transportation means will be added or excluded - Picture 3

The problem begins once the pivot table is refreshed. If new entry concerning the field Vegetables is added to our data base (ie.: Beetroot), it will not be processed in the final result, even though the privot tables are all refreshed. One could perceive that this new data "beetroot" was not included in our report. Note that by accessing PivotTable > Row Labels > Vegetables. We can realise that the "beetroot" was not automatically selected - Picture 4

So as to remedy this inconvenience, just access PivotTable > Row Labels > Vegetables > Filed configuration and, finally, tick the box "Select new items into the manual field" - Pictures 5,6.

Finally, the next time new entries to the field Vegetables are added and the pivot table is refreshed, the only veggie that is not going to be available is the Potato (as planned !). As to conclude, do not forget to select PivotTable Options > Data > and choose "NONE" at the "Number of items to retain per field". This setting is automatic by default and changing it to None will prevent the PivotTable retain items in the drop-down lists that are no longer there in our updated database - Picture 7

VBA PivotTables and coding

1 Answers1