Results 1 to 6 of 6

Thread: Selecting Slicer Items

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Sep 2015
    Posts
    2
    Location

    Selecting Slicer Items

    I have problem with my VBA code in excel. VBA code is used for selecting the last date during opening Excel file in slicer.
    The code, error and slicer are in PDF file you can to see in enclose.

    The mistake is on row:

    .SlicerItems("16.2.2015").Selected =True


    (Mistake is: runtime error 5 Invalid procedure call or argument.)


    Sub GroundHogDay()
        Application.ScreenUpdating =False
        Application.EnableEvents =False
        Application.Calculation =False
        ManualUpdate =False
        Application.Calculation = xlCalculationManual
        Dim today AsDate
        today = Now
        Dim todayString AsString
        todayString = Format$(today,"dd.mm.yyyy")
        Dim Item As SlicerItem
        ThisWorkbook.SlicerCaches("Průřez_ProcessingDate").ClearManualFilter
        With ActiveWorkbook.SlicerCaches("Průřez_ProcessingDate")
             'earliest data available in the data
             ***.SlicerItems("16.2.2015").Selected = True    ------------- here is error***
        EndWith
        For Each Item In               
            ThisWorkbook.SlicerCaches("Průřez_ProcessingDate").SlicerItems
            If Item.Name = todayString Then
                Item.Selected =True
            Else
                Item.Selected =False
            EndIf
        Next Item
        ThisWorkbook.SlicerCaches("Průřez_ProcessingDate1").ClearManualFilter
        With ActiveWorkbook.SlicerCaches("Průřez_ProcessingDate1")
             'earliest data available in the data
             **.SlicerItems("16.2.2015").Selected =True   
        EndWith
        For Each Item In   
            ThisWorkbook.SlicerCaches("Průřez_ProcessingDate1").SlicerItems
            If Item.Name = todayString Then
                Item.Selected =True
            Else
                Item.Selected =False
            EndIf
            Next Item
            ThisWorkbook.SlicerCaches("Průřez_DatExp").ClearManualFilter
            With ActiveWorkbook.SlicerCaches("Průřez_DatExp")
                 'earliest data available in the data
                 .SlicerItems("16.2.2015").Selected =True
            End With
            For Each Item In ThisWorkbook.SlicerCaches("Průřez_DatExp").SlicerItems
                If Item.Name = todayString Then
                    Item.Selected =True
                Else
                    Item.Selected =False
                EndIf
            Next Item
            Application.ScreenUpdating =True
            Application.EnableEvents =True
            Application.Calculation =True
            ManualUpdate =True
            Application.Calculation = xlCalculationAutomatic
            ThisWorkbook.RefreshAll
    EndSub
    Attached Images Attached Images
    Last edited by Aussiebear; 03-10-2025 at 07:16 AM. Reason: for better reading

Tags for this Thread

Posting Permissions

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