Consulting

Results 1 to 6 of 6

Thread: Sleeper: Selecting Slicer Items

Threaded View

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

    Sleeper: 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
    ForEach 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
    ForEach 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
    EndWith
    ForEach 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 Ladajakl; 09-25-2015 at 02: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
  •