PDA

View Full Version : Selecting Slicer Items



Ladajakl
09-25-2015, 01:41 AM
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

snb
09-25-2015, 01:59 AM
Please use a thread title that does reflect the content of your question.

Ladajakl
09-25-2015, 02:17 AM
Unfortunatelly, I do not know how I can change title.

SamT
09-25-2015, 08:02 AM
Bump for new Title and move to different Forum

Paul_Hossler
11-11-2015, 09:11 AM
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

CrystalR
03-06-2018, 11:08 AM
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



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