Consulting

Results 1 to 6 of 6

Thread: Selecting Slicer Items

  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

    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 Guru
    Joined
    Apr 2012
    Posts
    4,391
    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 Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    Bump for new Title and move to different Forum
    Please take the time to read the Forum FAQ

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,861
    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

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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
  •