2

I have a particular problem and couldn't find any solution anywhere on the internet.

So I have a pivot table which is connected to 6 slicers and also a chart which data range is dependent on pivot table values.

I've made a macro which updates chart scales everytime a value in any of the worksheet cells is changed. Here is the macro:

Public Sub worksheet_Change(ByVal Target2 As Range)
If ActiveSheet.Name = "Dashboard" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DataEntryMode = xlOff
 'Chart_axis Macro

Sheets("Dashboard").ChartObjects("Chart 9").Activate
If ActiveSheet.Range("B19") = "excluding CE" Then
    ActiveChart.Axes(xlValue).MinimumScale = Range("E3").Value
    ActiveChart.Axes(xlValue).MaximumScale = Range("E4").Value
Else
    ActiveChart.Axes(xlValue).MinimumScale = Range("A3").Value
    ActiveChart.Axes(xlValue).MaximumScale = Range("A4").Value
End If

ActiveChart.Refresh
ActiveSheet.Range("B18").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

In order to work as intended i also had to made a function which reads the active elements of a slicer:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
Application.Volatile
Set coll = New Collection
Dim cache As Excel.SlicerCache
Dim i As Integer
Set cache = ActiveWorkbook.SlicerCaches(SlicerName)
Dim sItem As Excel.SlicerItem
Dim result As String
For Each sItem In cache.SlicerItems
If sItem.Selected And sItem.HasData Then
    'Debug.Print sItem.Name
    'Debug.Print sItem.HasData
    'GetSelectedSlicerItems = (sItem.Name)
    coll.Add sItem.Name
    End If
Next sItem
For i = 1 To coll.Count
'Debug.Print coll(i)
 result = result & coll(i) & ", "
Next i
result = Left(result, Len(result) - 2)
GetSelectedSlicerItems = result
End Function

My problem is that while the value of the function always updates when the slicer item is changed, the macro only does it randomly about 50% of the time.

Screenshot of my report:

img1

The formulas containing the selected slicer items function are on the top right.

So do you have any idea how to make it work 100% of the time?

Thanks in advance,

Alan

Edit: i forgot to add that it's only the issue if only one slicer is highlited. When i select multiple slicers (with ctrl+click) it always works.

Alan K
  • 81
  • 7
  • So are you saying the `Worksheet_Change` event isn't being triggered when the value in cell X1 changes? – dwirony Aug 20 '18 at 15:17
  • Does it only work when you choose different slicers? Like, if you change the same slicer 3 times in a row does it only work the first time? – n8. Aug 20 '18 at 15:52
  • Yes, the values of the cells change, but the macro doesn't trigger. – Alan K Aug 20 '18 at 16:40
  • If I click the same slicer item again, nothing changes, but when i change the item then i change it back to the one i had highlited before it sometimes trigger and sometimes not. – Alan K Aug 20 '18 at 16:41
  • When the macro does NOT trigger, do the slicers you selected actually change the filtering on the pivot tables? – Eliot K Aug 20 '18 at 17:36
  • Yes, the chart data also changes except for the minimum and maximum scales which are meant to be changed by the macro. – Alan K Aug 20 '18 at 18:32

0 Answers0