Register
Log In
Unanswered
Home
December 12, 2016
Offline
I got 4 slicers - "Class", "Course", "Presenter" and "Topic" to update an identical pivot table. It's not until the "Topic" slicer being selected the target value in cell I43 won't be finalized. Only when one and only one single topic has been chosed then triger the Workbook_SheetPivotTableUpdate Event . How can I modify the VBA script?
Alternatively, if I use Worksheet_PivotTableUpdate event other than above mentioned Workbook_SheetPivotTableUpdate, how to select the inactive worksheet and change the code accordingly?
and the last but not least, it must be a way to run the VBA behind the scenes making it invisible while the cursor moves around the worksheet, right? How to make it happen?
Julian
See Answer
Dashboards
June 25, 2016
Offline
Hi Julian
You can put this code in the TulList worksheet module.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.ScreenUpdating = False 'disable screen updating
If Application.CountA(Range("I43:I44")) = 1 Then
MsgBox "One item" 'Do whatever you want here
End If
Application.ScreenUpdating = True 'enable screen updating
End Sub
It just count the entries in range I43 to I44 to determine if there are more than 1 item present. No need to use complicated VBA to get the visible list of the PivotTable.
Although you wanted the count after selecting the Topic slicer, selecting other slicers first may also trigger the event if the count so happen to be 1 e.g. you selected Presenter Matt Masson without selecting other slicers.
To prevent flickering of the screen you can use the Application.ScreenUpdating=False/True to switch off/on the screen refreshing.
Hope this is what you are looking for.
Sunny
Answers Post
December 12, 2016
Offline
Hi Sunny,
Frankly speaking I've posted that thread on MSOFFICE FORUMS earlier in last month end. Eventhough I knew very well if I've raised this case for your help, that issue would not be an issue any more in a short time. However, in order not to bother you too much, I've been waiting for a solution from some one else. Now that two weeks past, and finally I still need to solicit your support. Thank you again for your kindness and marvelous solution. This time I use the following codes to play VLC media player with script embeded.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Application.CountA(Range("I43:I44")) = 1 Then
play
End If
End Sub
Sub play()
Application.ScreenUpdating = False 'disable screen updating
With ActiveSheet
.Hyperlinks.Add Anchor:=.Range("J43"), _
Address:=Range("I43").Value, _
ScreenTip:="Play Video", _
TextToDisplay:="Watch"
Range("J43").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End With
Application.ScreenUpdating = True 'enable screen updating
End Sub
Best regards,
Julian
Most Users Ever Online: 57
Currently Online: Mynda Treacy
14 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
