Consulting

Results 1 to 6 of 6

Thread: Sleeper: Selecting Slicer Items

Hybrid 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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,626
    Please use a thread title that does reflect the content of your question.

  3. #3
    VBAX Newbie
    Joined
    Sep 2015
    Posts
    2
    Location
    Unfortunatelly, I do not know how I can change title.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,817
    Location
    Bump for new Title and move to different Forum
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,694
    Location
    To select the last Slicer item, try something like this

    Sub test()
        Dim i As Long, n As Long
        
        With ActiveWorkbook.SlicerCaches("Slicer_Account")
            n = .SlicerItems.Count
            
            For i = 1 To n - 1
                .SlicerItems(i).Selected = False
            Next I
            
            .SlicerItems(n).Selected = True
        End With
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    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

  6. #6
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    1
    Location
    I could kiss you!!! i've been looking for this FOREVER!!!! THANK YOU!!!! but i want the first thing to be selected, so im going with

    Sub Update_Slicer_Date()
        Dim i As Long, n As Long
        
        With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Week")
            n = .SlicerItems.Count
            
            For i = 2 To n - 0
                .SlicerItems(i).Selected = False
            Next i
            
            .SlicerItems(n).Selected = False
        End With
        
            With ActiveWorkbook.SlicerCaches("Slicer_Day_of_Date")
            n = .SlicerItems.Count
            
            For i = 2 To n - 0
                .SlicerItems(i).Selected = False
            Next i
            
            .SlicerItems(n).Selected = False
        End With
    End Sub
    Quote Originally Posted by Paul_Hossler View Post
    To select the last Slicer item, try something like this

    Sub test()
        Dim i As Long, n As Long
        
        With ActiveWorkbook.SlicerCaches("Slicer_Account")
            n = .SlicerItems.Count
            
            For i = 1 To n - 1
                .SlicerItems(i).Selected = False
            Next I
            
            .SlicerItems(n).Selected = True
        End With
    End Sub

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
  •