Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110552 > unrolled thread

Pivot Table run-time error 1004 (PivotField class)

Back to article view | Back to microsoft.public.excel.programming

Contents

  Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-05 20:14 -0400
    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 06:55 -0400
    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:23 -0400
      Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:35 -0400
        Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-06 20:13 -0400
          Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 20:42 -0400
            Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 13:09 -0400
              Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-07 15:33 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 15:52 -0400
                  Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-08 14:02 -0400
                    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-08 16:08 -0400
                      Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 06:34 -0400
                        Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 16:29 -0400
                          Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 22:01 -0400
                            Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 23:10 -0400
                            Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-10 14:05 -0400
                              Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-10 22:10 -0400
                                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 00:03 -0400
                                  Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-11 05:30 -0400
                                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 10:46 -0400
                                  Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-14 09:09 -0400
                                    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-14 13:58 -0400
                                      Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-17 18:15 -0400
                                        Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-17 19:48 -0400

Page 1 of 2  [1] 2  Next page →

#110552 — Pivot Table run-time error 1004 (PivotField class)

Hi.  I've got Windows 7 and Windows 10, Excel 2016 (version 1708 build
8431.2236). I've got a pivot table on one sheet that is connected to a
data Table on another, and the pivot table has slicers connected to
it.  I'm trying to take a cell value that is on yet another sheet and
filter that pivot table with that value.  I want to use the
DoubleClick event, where the user double clicks the value, which loads
into the pivot table filter on the other sheet.  Seems simple enough,
and I've used the double click event before with success, but just not
with a pivot table.  There is a plethora of simple code out that
showing how to do that, but with all iterations of code, I keep
getting an error message that says, "Unable to set the CurrentPage
property of the PivotField class".  

I've checked all of my options in Excel, and I've tried this on other
computers with the same result, and I've looked at must be 100 other
forum and Google sites with no luck, so I must be doing something
wrong.  The first module shown below is that code.  Below that is
another module that a coworker made that does work, but it is SLOW!!  

I'd appreciate any help and advice.  Thank you!

===============

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
       
    Dim iCol As Long
    Dim myCatNum As String
    
    iCol = ActiveCell.Column

'case structure not needed, but borrowed from another procedure.
    Select Case iCol 
                    
        Case 4
            myCatNum = ActiveCell.Value
            Sheets("Slicers (all)").Select
 ActiveSheet.PivotTables("PivotSlicer").PivotFields("Catalog
#").ClearAllFilters
 ActiveSheet.PivotTables("PivotSlicer").PivotFields("Catalog
#").CurrentPage = myCatNum
       Case Else
            Exit Sub
    End Select

End Sub

=======================
'this one works...but it is SLOW at about 45 seconds to load the table
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
    
    Dim iCol As Long
    Dim myCatNum As String
    Dim pt As PivotTable
    Dim ptItem As PivotItem
    Dim Field As PivotField
    
    iCol = ActiveCell.Column
   
    Select Case iCol
                    
        Case 4
            myCatNum = ActiveCell.Value
            Sheets("Slicers (all)").Select
            Set pt = Worksheets("Slicers
(all)").PivotTables("PivotSlicer")
            Set Field = pt.PivotFields("Catalog #")
            
            Field.ClearAllFilters
            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            pt.RefreshTable
            
            Application.EnableEvents = False
            With Field
                For Each ptItem In .PivotItems
                    If ptItem = myCatNum Then
                        ptItem.Visible = True
                    Else: ptItem.Visible = False
                    End If
                Next
                Application.EnableEvents = True
            End With
    End Select
    
    pt.RefreshTable

End Sub

[toc] | [next] | [standalone]

#110553

Perhaps you'll find something helpful here...

  http://www.contextures.com/pivottableindex.html

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110554

OR you can try...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
  'Cancel EditMode and get Catalog Number
  sCatNum = Target.Value: Cancel = True
  With Sheets("Slicers (all)").PivotTables("PivotSlicer").PivotFields("Catalog 
#")
    .ClearAllFilters
	Application.EnableEvents = False
	For Each vItem In .PivotItems
	  vItem.Visible = (vItem = sCatNum)
	Next 'vItem
	Application.EnableEvents = True
  End With '...PivotFields("Catalog #")
End If '(Target.Column = 4)

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110555

Oops.., forgot to refresh...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
  'Cancel EditMode and get Catalog Number
  sCatNum = Target.Value: Cancel = True
  With Sheets("Slicers (all)").PivotTables("PivotSlicer")
	With .PivotFields("Catalog #")
	  .ClearAllFilters
	  Application.EnableEvents = False
	  For Each vItem In .PivotItems
	    vItem.Visible = (vItem = sCatNum)
	  Next 'vItem
	  Application.EnableEvents = True
	End With '.PivotFields("Catalog #")
	.RefreshTable
  End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
End If '(Target.Column = 4)

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110556

Hi Garry,

It's been a while since I've posted, and it's always a pleasure to
hear from you.  I plugged this in, and it does indeed run; however,
unfortunately, it's still about as slow as the other other code.  I
think it's because it's actually loading the pivot table with every
iteration of the loop.  And there are over 300 pivot items to load.  

When I actually go to the pivot table and select a catalog number from
the slicer, it loads in just a couple seconds.  Is there a reason I'm
not able to simply clear the filters and load the catalog number that
gets put into the variable from the double-click event?  

Thanks Garry!

Frank

On Fri, 06 Apr 2018 07:35:45 -0400, GS <gs@v.invalid> wrote:

>Oops.., forgot to refresh...
>
>Dim sCatNum$, vItem
>If (Target.Column = 4) Then
>  'Cancel EditMode and get Catalog Number
>  sCatNum = Target.Value: Cancel = True
>  With Sheets("Slicers (all)").PivotTables("PivotSlicer")
>	With .PivotFields("Catalog #")
>	  .ClearAllFilters
>	  Application.EnableEvents = False
>	  For Each vItem In .PivotItems
>	    vItem.Visible = (vItem = sCatNum)
>	  Next 'vItem
>	  Application.EnableEvents = True
>	End With '.PivotFields("Catalog #")
>	.RefreshTable
>  End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
>End If '(Target.Column = 4)

[toc] | [prev] | [next] | [standalone]

#110557

> Hi Garry,
>
> It's been a while since I've posted, and it's always a pleasure to
> hear from you.  I plugged this in, and it does indeed run; however,
> unfortunately, it's still about as slow as the other other code.  I
> think it's because it's actually loading the pivot table with every
> iteration of the loop.  And there are over 300 pivot items to load.  
>
> When I actually go to the pivot table and select a catalog number from
> the slicer, it loads in just a couple seconds.  Is there a reason I'm
> not able to simply clear the filters and load the catalog number that
> gets put into the variable from the double-click event?  
>
> Thanks Garry!
>
> Frank
>
> On Fri, 06 Apr 2018 07:35:45 -0400, GS <gs@v.invalid> wrote:
>
>> Oops.., forgot to refresh...
>> 
>> Dim sCatNum$, vItem
>> If (Target.Column = 4) Then
>>  'Cancel EditMode and get Catalog Number
>>  sCatNum = Target.Value: Cancel = True
>>  With Sheets("Slicers (all)").PivotTables("PivotSlicer")
>> 	With .PivotFields("Catalog #")
>> 	  .ClearAllFilters
>> 	  Application.EnableEvents = False
>> 	  For Each vItem In .PivotItems
>> 	    vItem.Visible = (vItem = sCatNum)
>> 	  Next 'vItem
>> 	  Application.EnableEvents = True
>> 	End With '.PivotFields("Catalog #")
>> 	.RefreshTable
>>  End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
>> End If '(Target.Column = 4)

The pivot table functions are written in C++ which is *orders of magnitude* 
faster than VB. I was hoping the link I posted would contain some way to 
automate the slicer. Optionally, turn on the macro recorder and see what code 
(if any) it generates when you do it manually.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110558

On Fri, 06 Apr 2018 20:42:10 -0400, GS <gs@v.invalid> wrote:

>> Hi Garry,
>>
>> It's been a while since I've posted, and it's always a pleasure to
>> hear from you.  I plugged this in, and it does indeed run; however,
>> unfortunately, it's still about as slow as the other other code.  I
>> think it's because it's actually loading the pivot table with every
>> iteration of the loop.  And there are over 300 pivot items to load.  
>>
>> When I actually go to the pivot table and select a catalog number from
>> the slicer, it loads in just a couple seconds.  Is there a reason I'm
>> not able to simply clear the filters and load the catalog number that
>> gets put into the variable from the double-click event?  
>>
>> Thanks Garry!
>>
>> Frank
>>
>> On Fri, 06 Apr 2018 07:35:45 -0400, GS <gs@v.invalid> wrote:
>>
>>> Oops.., forgot to refresh...
>>> 
>>> Dim sCatNum$, vItem
>>> If (Target.Column = 4) Then
>>>  'Cancel EditMode and get Catalog Number
>>>  sCatNum = Target.Value: Cancel = True
>>>  With Sheets("Slicers (all)").PivotTables("PivotSlicer")
>>> 	With .PivotFields("Catalog #")
>>> 	  .ClearAllFilters
>>> 	  Application.EnableEvents = False
>>> 	  For Each vItem In .PivotItems
>>> 	    vItem.Visible = (vItem = sCatNum)
>>> 	  Next 'vItem
>>> 	  Application.EnableEvents = True
>>> 	End With '.PivotFields("Catalog #")
>>> 	.RefreshTable
>>>  End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
>>> End If '(Target.Column = 4)
>
>The pivot table functions are written in C++ which is *orders of magnitude* 
>faster than VB. I was hoping the link I posted would contain some way to 
>automate the slicer. Optionally, turn on the macro recorder and see what code 
>(if any) it generates when you do it manually.

Thanks Garry.  I haven't had a chance to look at the C++ in the link
yet.  Will do that tonight.  In the meantime, I recorded the macro,
and below is an excerpt of what it returns.  There are actually over
300 items in the list.  So, the macro above is looping through each of
those items, as the code below more or less is doing, but the code
above takes about 45 seconds to load, whereas the recorded code below
is almost instantaneous.  Is there not a way to turn off all the
slicer items at once, and then turn on just the one I need?  That's
what I've been looking for but have not been able to find.  Thanks
Garry.  


Sub Macro1()
    With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
       If mySlicerItem = .SlicerItems("277096275") Then .Selected =
True
        .SlicerItems("277096276").Selected = False
        .SlicerItems("277096325").Selected = False
        .SlicerItems("1100000000").Selected = False
        .SlicerItems("1900013000").Selected = False
        .SlicerItems("1900031000").Selected = False
        .SlicerItems("2108100000").Selected = False
        .SlicerItems("2108105000").Selected = False
        .SlicerItems("2108151000").Selected = False
        .SlicerItems("2108352000").Selected = False
        .SlicerItems("2296003108").Selected = False
        .SlicerItems("2296003111").Selected = False
        .SlicerItems("2296003125").Selected = False
        .SlicerItems("2296003511").Selected = False
        .SlicerItems("2296301000").Selected = False
    End With
End Sub

[toc] | [prev] | [next] | [standalone]

#110559

> Thanks Garry.  I haven't had a chance to look at the C++ in the link
> yet.  Will do that tonight.

There's no C++ at the link; -it's pure Excel and/or VBA samples/examples about 
working with PivotTables.

>  In the meantime, I recorded the macro,
> and below is an excerpt of what it returns.  There are actually over
> 300 items in the list.  So, the macro above is looping through each of
> those items, as the code below more or less is doing, but the code
> above takes about 45 seconds to load, whereas the recorded code below
> is almost instantaneous.  Is there not a way to turn off all the
> slicer items at once, and then turn on just the one I need?  That's
> what I've been looking for but have not been able to find.  Thanks
> Garry.  
>
>
> Sub Macro1()
>     With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
>        If mySlicerItem = .SlicerItems("277096275") Then .Selected =
> True
>         .SlicerItems("277096276").Selected = False
>         .SlicerItems("277096325").Selected = False
>         .SlicerItems("1100000000").Selected = False
>         .SlicerItems("1900013000").Selected = False
>         .SlicerItems("1900031000").Selected = False
>         .SlicerItems("2108100000").Selected = False
>         .SlicerItems("2108105000").Selected = False
>         .SlicerItems("2108151000").Selected = False
>         .SlicerItems("2108352000").Selected = False
>         .SlicerItems("2296003108").Selected = False
>         .SlicerItems("2296003111").Selected = False
>         .SlicerItems("2296003125").Selected = False
>         .SlicerItems("2296003511").Selected = False
>         .SlicerItems("2296301000").Selected = False
>     End With
> End Sub

Have you looked in the ObjectBrowser to see what properties/methods a 
PivotTable exposes to VBA?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110560

On Sat, 07 Apr 2018 15:33:02 -0400, GS <gs@v.invalid> wrote:

>> Thanks Garry.  I haven't had a chance to look at the C++ in the link
>> yet.  Will do that tonight.
>
>There's no C++ at the link; -it's pure Excel and/or VBA samples/examples about 
>working with PivotTables.
>
>>  In the meantime, I recorded the macro,
>> and below is an excerpt of what it returns.  There are actually over
>> 300 items in the list.  So, the macro above is looping through each of
>> those items, as the code below more or less is doing, but the code
>> above takes about 45 seconds to load, whereas the recorded code below
>> is almost instantaneous.  Is there not a way to turn off all the
>> slicer items at once, and then turn on just the one I need?  That's
>> what I've been looking for but have not been able to find.  Thanks
>> Garry.  
>>
>>
>> Sub Macro1()
>>     With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
>>        If mySlicerItem = .SlicerItems("277096275") Then .Selected =
>> True
>>         .SlicerItems("277096276").Selected = False
>>         .SlicerItems("277096325").Selected = False
>>         .SlicerItems("1100000000").Selected = False
>>         .SlicerItems("1900013000").Selected = False
>>         .SlicerItems("1900031000").Selected = False
>>         .SlicerItems("2108100000").Selected = False
>>         .SlicerItems("2108105000").Selected = False
>>         .SlicerItems("2108151000").Selected = False
>>         .SlicerItems("2108352000").Selected = False
>>         .SlicerItems("2296003108").Selected = False
>>         .SlicerItems("2296003111").Selected = False
>>         .SlicerItems("2296003125").Selected = False
>>         .SlicerItems("2296003511").Selected = False
>>         .SlicerItems("2296301000").Selected = False
>>     End With
>> End Sub
>
>Have you looked in the ObjectBrowser to see what properties/methods a 
>PivotTable exposes to VBA?


Hi Garry,

Sorry, I misunderstood about the link.  I just looked at it, and wow!
There is a lot of pivot table stuff in there!  I'm going through it
now and have found a few things that might work.  I'll follow-up here
and let you know what I come up with.  

Regarding your last comment about the ObjectBrowser, I did not think
to do that.  I'll plug away tonight and take a look there too.  

Thanks Garry.  Back at you soon. 

[toc] | [prev] | [next] | [standalone]

#110561

Just posting back on my findings.  I tried many of the variations that
were in that link, and the closest I got was to actually get the pivot
table to quickly filter on my catalog number variable.  However, it
left the attached slicers untouched.  And several things I tried on
the slicers worked, but every option took an inordinate amount of
time.  That's presumably because of the 300+ pivot items the code is
looping through.  

I'm able to use .ClearAllFilters to reset everything, so it would be
nice if I was able to .AddAllFiltersExcept without doing a loop, but I
don't think that exists.  It should, though, because there are about a
zillion posts out there from people like me looking for something very
similar.  

I think I've hit a dead end.  Any other thoughts or possibilities?

Thanks Garry (and anyone else). 

Frank

On Sat, 07 Apr 2018 15:52:06 -0400, Phrank <pbeal@hotmail.com> wrote:

>On Sat, 07 Apr 2018 15:33:02 -0400, GS <gs@v.invalid> wrote:
>
>>> Thanks Garry.  I haven't had a chance to look at the C++ in the link
>>> yet.  Will do that tonight.
>>
>>There's no C++ at the link; -it's pure Excel and/or VBA samples/examples about 
>>working with PivotTables.
>>
>>>  In the meantime, I recorded the macro,
>>> and below is an excerpt of what it returns.  There are actually over
>>> 300 items in the list.  So, the macro above is looping through each of
>>> those items, as the code below more or less is doing, but the code
>>> above takes about 45 seconds to load, whereas the recorded code below
>>> is almost instantaneous.  Is there not a way to turn off all the
>>> slicer items at once, and then turn on just the one I need?  That's
>>> what I've been looking for but have not been able to find.  Thanks
>>> Garry.  
>>>
>>>
>>> Sub Macro1()
>>>     With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
>>>        If mySlicerItem = .SlicerItems("277096275") Then .Selected =
>>> True
>>>         .SlicerItems("277096276").Selected = False
>>>         .SlicerItems("277096325").Selected = False
>>>         .SlicerItems("1100000000").Selected = False
>>>         .SlicerItems("1900013000").Selected = False
>>>         .SlicerItems("1900031000").Selected = False
>>>         .SlicerItems("2108100000").Selected = False
>>>         .SlicerItems("2108105000").Selected = False
>>>         .SlicerItems("2108151000").Selected = False
>>>         .SlicerItems("2108352000").Selected = False
>>>         .SlicerItems("2296003108").Selected = False
>>>         .SlicerItems("2296003111").Selected = False
>>>         .SlicerItems("2296003125").Selected = False
>>>         .SlicerItems("2296003511").Selected = False
>>>         .SlicerItems("2296301000").Selected = False
>>>     End With
>>> End Sub
>>
>>Have you looked in the ObjectBrowser to see what properties/methods a 
>>PivotTable exposes to VBA?
>
>
>Hi Garry,
>
>Sorry, I misunderstood about the link.  I just looked at it, and wow!
>There is a lot of pivot table stuff in there!  I'm going through it
>now and have found a few things that might work.  I'll follow-up here
>and let you know what I come up with.  
>
>Regarding your last comment about the ObjectBrowser, I did not think
>to do that.  I'll plug away tonight and take a look there too.  
>
>Thanks Garry.  Back at you soon. 

[toc] | [prev] | [next] | [standalone]

#110562

> Just posting back on my findings.  I tried many of the variations that
> were in that link, and the closest I got was to actually get the pivot
> table to quickly filter on my catalog number variable.  However, it
> left the attached slicers untouched.  And several things I tried on
> the slicers worked, but every option took an inordinate amount of
> time.  That's presumably because of the 300+ pivot items the code is
> looping through.
>
> I'm able to use .ClearAllFilters to reset everything, so it would be
> nice if I was able to .AddAllFiltersExcept without doing a loop, but I
> don't think that exists.  It should, though, because there are about a
> zillion posts out there from people like me looking for something very
> similar.
>
> I think I've hit a dead end.  Any other thoughts or possibilities?
>
> Thanks Garry (and anyone else).
>
> Frank

I've never used PivotTables so perhaps a dumb Q here:

  Once you ClearAllFilters, can you not just add the one you want to use?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110565

On Sun, 08 Apr 2018 16:08:17 -0400, GS <gs@v.invalid> wrote:

>> Just posting back on my findings.  I tried many of the variations that
>> were in that link, and the closest I got was to actually get the pivot
>> table to quickly filter on my catalog number variable.  However, it
>> left the attached slicers untouched.  And several things I tried on
>> the slicers worked, but every option took an inordinate amount of
>> time.  That's presumably because of the 300+ pivot items the code is
>> looping through.
>>
>> I'm able to use .ClearAllFilters to reset everything, so it would be
>> nice if I was able to .AddAllFiltersExcept without doing a loop, but I
>> don't think that exists.  It should, though, because there are about a
>> zillion posts out there from people like me looking for something very
>> similar.
>>
>> I think I've hit a dead end.  Any other thoughts or possibilities?
>>
>> Thanks Garry (and anyone else).
>>
>> Frank
>
>I've never used PivotTables so perhaps a dumb Q here:
>
>  Once you ClearAllFilters, can you not just add the one you want to use?

That's actually what I thought too and what I originally did, but I
get that PivotField class run-time error. I did just figure out the
run-time error problem, and the new code is below. Moving the
.SlicerItems out f the pivot table with structure fixed that error,
and the code below runs....but it still doesn't work.  It seems to
load the NewCat variable sliceritem, but all of the other 300+ slicer
items also remain selected.  It seems there would be a way to DESELECT
all items EXCEPT the one defined without having to do a loop.  And
that's been the elusive bit of code.  Thanks for looking at this,
Garry. 

Sub test()
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Slicers (all)").PivotTables("PivotSlicer")
Set Field = pt.PivotFields("Catalog #")
NewCat = "5407120450" 

With Field
    .ClearAllFilters
End With
        
With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
    .SlicerItems(NewCat).Selected = True
End With

pt.RefreshTable

End Sub

[toc] | [prev] | [next] | [standalone]

#110566

Maybe the property setting you need to use is "DrilledDown" instead of 
"Visible". I can't play around with this not having a pivot table to work with; 
-post a download link to your file if you'd like me to see if I can figure 
something out...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110567

Will do, and I'd appreciate that.  If I can get this to work, it will
save a chunk of time and improve quality for folks in my department
(because they won't need to look at a catalog number on one sheet,
then go to the other sheet and fat-finger it in.  

Here is a link to the workbook in my DropBox account.  Thanks very
much Garry!

https://www.dropbox.com/s/3ykj12489z7c2qk/Slicers%20Plus%20for%20Office%20Keys.xlsm?dl=0


On Mon, 09 Apr 2018 16:29:20 -0400, GS <gs@v.invalid> wrote:

>Maybe the property setting you need to use is "DrilledDown" instead of 
>"Visible". I can't play around with this not having a pivot table to work with; 
>-post a download link to your file if you'd like me to see if I can figure 
>something out...

[toc] | [prev] | [next] | [standalone]

#110568

> Will do, and I'd appreciate that.  If I can get this to work, it will
> save a chunk of time and improve quality for folks in my department
> (because they won't need to look at a catalog number on one sheet,
> then go to the other sheet and fat-finger it in.  
>
> Here is a link to the workbook in my DropBox account.  Thanks very
> much Garry!
>
> https://www.dropbox.com/s/3ykj12489z7c2qk/Slicers%20Plus%20for%20Office%20Keys.xlsm?dl=0
>
>
> On Mon, 09 Apr 2018 16:29:20 -0400, GS <gs@v.invalid> wrote:
>
>> Maybe the property setting you need to use is "DrilledDown" instead of 
>> "Visible". I can't play around with this not having a pivot table to work 
>> with;  -post a download link to your file if you'd like me to see if I can 
>> figure  something out...

Got it!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110569

Ok Phrank, this puts me back to where/why I don't use Table objects; -doing 
anything with them is pathetically slow! Sorry I can't help further...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110570

Hi Garry,

Agreed.  I really appreciate you taking a look and trying.  In the
meantime today, I had a thought for a sort of work around.  It's not
perfect, but it's close.  I'm still using the double-click event on
the Output sheet, which is putting the selected catalog number into a
variable, switching to the slicer sheet, putting that variable into a
specified cell, and the copying that cell to the clipboard.  Then the
user doesn't have to remember or fatfinger in the number, but rather
just select the pivot table drop down and paste into the search field.
It works.  

Again, THANK YOU for all your time and attention to my issue!  

Frank

On Tue, 10 Apr 2018 14:05:43 -0400, GS <gs@v.invalid> wrote:

>Ok Phrank, this puts me back to where/why I don't use Table objects; -doing 
>anything with them is pathetically slow! Sorry I can't help further...

[toc] | [prev] | [next] | [standalone]

#110571

> Hi Garry,
>
> Agreed.  I really appreciate you taking a look and trying.  In the
> meantime today, I had a thought for a sort of work around.  It's not
> perfect, but it's close.  I'm still using the double-click event on
> the Output sheet, which is putting the selected catalog number into a
> variable, switching to the slicer sheet, putting that variable into a
> specified cell, and the copying that cell to the clipboard.  Then the
> user doesn't have to remember or fatfinger in the number, but rather
> just select the pivot table drop down and paste into the search field.
> It works.  
>
> Again, THANK YOU for all your time and attention to my issue!  
>
> Frank

Interestingly, I was looking at a Contextures solution that does something 
similar via a userform. You might want to persist checking that site out...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110572

Will do.  That site did have a lot of great info!  Thanks again!

On Wed, 11 Apr 2018 00:03:19 -0400, GS <gs@v.invalid> wrote:

>> Hi Garry,
>>
>> Agreed.  I really appreciate you taking a look and trying.  In the
>> meantime today, I had a thought for a sort of work around.  It's not
>> perfect, but it's close.  I'm still using the double-click event on
>> the Output sheet, which is putting the selected catalog number into a
>> variable, switching to the slicer sheet, putting that variable into a
>> specified cell, and the copying that cell to the clipboard.  Then the
>> user doesn't have to remember or fatfinger in the number, but rather
>> just select the pivot table drop down and paste into the search field.
>> It works.  
>>
>> Again, THANK YOU for all your time and attention to my issue!  
>>
>> Frank
>
>Interestingly, I was looking at a Contextures solution that does something 
>similar via a userform. You might want to persist checking that site out...

[toc] | [prev] | [next] | [standalone]

#110573

Since I do table data analysis in a userform I never did check to see what 
effect my EnableFastCode routine has on VBA processing of tables. Give that 
tables do a lot of automatic recalcs, this will suspend all background 
processes until your process is finished. You could give it a shot to see if it 
speeds things up!

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a standard 
module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
'  'Default types
'  Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID 
As String
'  'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes' 
declarations

  'The following will make sure only the Caller has control,
  'and allows any Caller to take control when not in use.
  If AppMode.CallerID <> Caller Then _
    If AppMode.CallerID <> "" Then Exit Sub

  With Application
    If SetFast Then
      AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
      AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
      AppMode.Events = .EnableEvents: .EnableEvents = False
      AppMode.CallerID = Caller
    Else
      .ScreenUpdating = AppMode.Display
      .Calculation = AppMode.CalcMode
      .EnableEvents = AppMode.Events
      AppMode.CallerID = ""
    End If
  End With
End Sub

You use it like this:
  Sub DoThis()
  Const sSrc$ = "DoThis" '//name of procedure

  EnableFastCode sSrc
  'Do some overhead intensive stuff
  EnableFastCode sSrc, False
  End Sub

How it Works:
This is a concept-based methodology where the control belongs to only 1 Caller 
until that caller is done its task. That means other Callers using the settings 
can't inadvertently toggle them while another operation is using them.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web