Consulting

Results 1 to 5 of 5

Thread: Selecting Slicer Items

  1. #1

    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Last edited by Ladajakl; 09-25-2015 at 02:16 AM. Reason: for better reading

  2. #2
    Please use a thread title that does reflect the content of your question.

  3. #3
    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,310
    Location
    Bump for new Title and move to different Forum
    Please take the time to read the Forum FAQ

  5. #5
    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 
    
    
    Formatting tags added by mark007
    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

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
  •