Auto-populate slicer with hovering cursor

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Auto-populate slicer with hovering cursor

Post by jstevens »

Is it possible to auto-populate a slicer based on a hovering cursor over a Pivot Chart item?

I have attached a sample workbook.
el_Slicer_PivotChart.xlsx
Your suggestions are appreciated.
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

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

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Auto-populate slicer with hovering cursor

Post by jstevens »

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.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

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

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Auto-populate slicer with hovering cursor

Post by jstevens »

Hans,

MouseDown or MouseUp event would be fine.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

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

el_Slicer_PivotChart.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Auto-populate slicer with hovering cursor

Post by jstevens »

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.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

It's complicated because the slicer items are not in the same order as the row labels of the pivot table...
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

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

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Auto-populate slicer with hovering cursor

Post by jstevens »

Hans,

Your suggestion worked perfectly. I have attached the file with the latest code modifications for those interested.

Many thanks!
el_Slicer_PivotChart.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Auto-populate slicer with hovering cursor

Post by HansV »

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