PDA

View Full Version : [SOLVED] Code for Hiding Slicers on Pivot Table: Getting Error Message



Jim Clayton
06-21-2018, 08:02 AM
Hello. I have some code that is used for hiding slicers (below). I got it using a test sheet. I've reformatted it to try and apply it to the workbook that it's intended to be used for (attached), but I'm getting the following error message:
Compile Error: Ambiguous Name Detected: Worksheet_PivotTableUpdate
Would greatly appreciate if someone could look at this and tell me where I'm going wrong. Please and Thank You.


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'
If Not ActiveWorkbook.SlicerCaches("Slicer_Size").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Size").Visible = True
Else
ActiveSheet.Shapes("Size").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_Type").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Type").Visible = True
Else
ActiveSheet.Shapes("Type").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_Rating").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Rating").Visible = True
Else
ActiveSheet.Shapes("Rating").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_Schedule").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Schedule").Visible = True
Else
ActiveSheet.Shapes("Schedule").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_NPT").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("NPT").Visible = True
Else
ActiveSheet.Shapes("NPT").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_Gauge").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Gauge").Visible = True
Else
ActiveSheet.Shapes("Gauge").Visible = False
End If
If Not ActiveWorkbook.SlicerCaches("Slicer_Model").SlicerItems.Item(1).Value = "N/A" Then
ActiveSheet.Shapes("Model").Visible = True
Else
ActiveSheet.Shapes("Model").Visible = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Paul_Hossler
06-21-2018, 08:24 AM
I don't see a problem with the attachment, but then I don't see the macro in #1 either

The message means that the sub is defined more than once in the same scope (module)

Jim Clayton
06-21-2018, 08:38 AM
I failed to mention that. I made a duplicate of the sheet without the macro in it until I got all of the kinks worked out. Advice going forward? Not great with VBA. Tks.