none
VBA code - if slicer is included on Excel sheet RRS feed

  • Question

  • Within VBA I would like to check if specific slicers are included (visible).  I'm setting up a macro to add/delete specific slicers based on criteria, so need to check if a slicer is already included on sheet.  The below "true" statement isn't working.

    In below example, if PO_DEV_TEAM slicer is already included on sheet, then delete it.

      If ActiveSheet.Shapes.Range(Array("PO_DEV_TEAM")).Visible = True Then

        ActiveSheet.Shapes.Range(Array("PO_DEV_TEAM")).Select
        Selection.Delete

     End If
       

    Thanks, Trina

    Thursday, April 13, 2017 3:09 PM

All replies

  • Hi Trina,

    You could useShapes.Item Method to get the slicer object and delete it directly. If there is no such slicer, you just need to handle the error.

    Here is the example

    Sub DeleteSlicer()
    On Error GoTo ErrorHandler
    ActiveSheet.Shapes.Item("PO_DEV_TEAM").Delete
    Exit Sub
    ErrorHandler:
    MsgBox "There is no such Slicer"
    End Sub
    

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 6:58 AM
    Moderator