Is it possible to auto-populate a slicer based on a hovering cursor over a Pivot Chart item?
I have attached a sample workbook.
Your suggestions are appreciated.
Auto-populate slicer with hovering cursor
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Auto-populate slicer with hovering cursor
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
The MouseMove event of a chart only fires after the user has clicked on the chart, not when the user simply moves the mouse over the chart without having clicked on it. That is probably not what you wanted...
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Auto-populate slicer with hovering cursor
Hans,
The MouseMove event may be an option. I can select the "Expense by Account" chart' category using VBA with "ActiveChart.Axes(xlCategory).Select". How would I capture the appropriate account "Fruit" having clicked on it? If I hover over the object, a bubble displays "Fruit (Account) Category: Fruit".
Perhaps there is an alternative method.
The MouseMove event may be an option. I can select the "Expense by Account" chart' category using VBA with "ActiveChart.Axes(xlCategory).Select". How would I capture the appropriate account "Fruit" having clicked on it? If I hover over the object, a bubble displays "Fruit (Account) Category: Fruit".
Perhaps there is an alternative method.
Regards,
John
John
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
If you want to capture clicking on a chart element, you can use the MouseDown (or MouseUp) event. Would that be OK?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Auto-populate slicer with hovering cursor
Hans,
MouseDown or MouseUp event would be fine.
MouseDown or MouseUp event would be fine.
Regards,
John
John
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
Here is an example. Click on one of the data points in the upper chart to select the corresponding slicer element.
The workbook is now a .xlsm
The workbook is now a .xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Auto-populate slicer with hovering cursor
Hans,
Your suggestion works based on hard coding the Case # on Arg2.
Is it possible to change the code to use a "collection" based on Arg2's Name ie the data point clicked upon? I'm thinking down the road a bit as the Data table expands and not manually updating the code each time for new data points.
Your suggestion works based on hard coding the Case # on Arg2.
Is it possible to change the code to use a "collection" based on Arg2's Name ie the data point clicked upon? I'm thinking down the road a bit as the Data table expands and not manually updating the code each time for new data points.
Regards,
John
John
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
It's complicated because the slicer items are not in the same order as the row labels of the pivot table...
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
Try changing the EventChart_MouseUp event procedure in the class module to
Code: Select all
Private Sub EventChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim XVals()
Dim SItm As SlicerItem
XVals = EventChart.SeriesCollection(1).XValues
Call EventChart.GetChartElement(x, y, ElementID, Arg1, Arg2)
With ActiveWorkbook.SlicerCaches("Slicer_Account")
.SlicerItems(XVals(Arg2)).Selected = True
For Each SItm In .SlicerItems
If SItm.Caption <> XVals(Arg2) Then
SItm.Selected = False
End If
Next SItm
End With
End Sub
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Auto-populate slicer with hovering cursor
Hans,
Your suggestion worked perfectly. I have attached the file with the latest code modifications for those interested.
Many thanks!
Your suggestion worked perfectly. I have attached the file with the latest code modifications for those interested.
Many thanks!
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Auto-populate slicer with hovering cursor
That was interesting. I originally thought it couldn't be done, but with a bit of online research and experimenting, it came together.
Best wishes,
Hans
Hans