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