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

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

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("Ca talog
#")
.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

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

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 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)

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 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

On Fri, 06 Apr 2018 20:42:10 -0400, GS 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 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

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

On Sat, 07 Apr 2018 15:33:02 -0400, GS 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.

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 wrote:

On Sat, 07 Apr 2018 15:33:02 -0400, GS 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.

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 he

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

On Sun, 08 Apr 2018 16:08:17 -0400, GS 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 he

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

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

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/3ykj12489z...Keys.xlsm?dl=0


On Mon, 09 Apr 2018 16:29:20 -0400, GS 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...

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/3ykj12489z...Keys.xlsm?dl=0


On Mon, 09 Apr 2018 16:29:20 -0400, GS 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

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

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 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...

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

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

On Wed, 11 Apr 2018 00:03:19 -0400, GS 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...

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

Hi Garry. Question. Is this different than what I currently do? I
have two modules, one that turns off application settings and one that
turns them back on, and I sandwich my code between the two modules.
i.e., first line in my sub is TurnOff and last line before End Sub is
TurnOn.


'Turn off application settings
Sub TurnOff()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.DisplayStatusBar = False
End With
End Sub

'Turn on application settings
Sub TurnOn()
With Application
.ScreenUpdating = True
.EnableEvents = True
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
End With
End Sub


On Wed, 11 Apr 2018 10:46:25 -0400, GS wrote:

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.

Hi Garry. Question. Is this different than what I currently do? I
have two modules, one that turns off application settings and one that
turns them back on, and I sandwich my code between the two modules.
i.e., first line in my sub is TurnOff and last line before End Sub is
TurnOn.


'Turn off application settings
Sub TurnOff()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.DisplayStatusBar = False
End With
End Sub

'Turn on application settings
Sub TurnOn()
With Application
.ScreenUpdating = True
.EnableEvents = True
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
End With
End Sub


On Wed, 11 Apr 2018 10:46:25 -0400, GS wrote:

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.

It stores existing (default) user settings BEFORE turning these off, AND
prohibits (locks out) any other procedures from messing with the settings until
the Caller releases access to the them.

The way yours works is a called procedure could inadvently toggle the settings
if another procedure is using them. IOW, it's missing the 'lockout' feature.
Consider this similar to more than 1 app having the same file open in that if
Sub1 is using the settings they become 'read only' to all other subs until Sub1
releases 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

Interesting. I obviously never thought of that. I'm in the middle of
a big project (which is what I was hoping to apply this pivot table
thing to), but after that is done in a couple weeks, I'll grab this
and modify my current process. I appreciate the tip and explanation.
Thanks for all your help trying to solve this. If I do happen to come
up with a decent work-around, I'll post back just as an FYI. Thanks!

Frank

On Sat, 14 Apr 2018 13:58:12 -0400, GS wrote:

Hi Garry. Question. Is this different than what I currently do? I
have two modules, one that turns off application settings and one that
turns them back on, and I sandwich my code between the two modules.
i.e., first line in my sub is TurnOff and last line before End Sub is
TurnOn.


'Turn off application settings
Sub TurnOff()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.DisplayStatusBar = False
End With
End Sub

'Turn on application settings
Sub TurnOn()
With Application
.ScreenUpdating = True
.EnableEvents = True
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
End With
End Sub


On Wed, 11 Apr 2018 10:46:25 -0400, GS wrote:

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.

It stores existing (default) user settings BEFORE turning these off, AND
prohibits (locks out) any other procedures from messing with the settings until
the Caller releases access to the them.

The way yours works is a called procedure could inadvently toggle the settings
if another procedure is using them. IOW, it's missing the 'lockout' feature.
Consider this similar to more than 1 app having the same file open in that if
Sub1 is using the settings they become 'read only' to all other subs until Sub1
releases them!

Interesting. I obviously never thought of that. I'm in the middle of
a big project (which is what I was hoping to apply this pivot table
thing to), but after that is done in a couple weeks, I'll grab this
and modify my current process. I appreciate the tip and explanation.
Thanks for all your help trying to solve this. If I do happen to come
up with a decent work-around, I'll post back just as an FYI. Thanks!

Frank

Always glad to help...
Best Wishes!

--
Garry

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