Consulting

Results 1 to 3 of 3

Thread: Code for Hiding Slicers on Pivot Table: Getting Error Message

  1. #1

    Code for Hiding Slicers on Pivot Table: Getting Error Message

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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    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)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •