Can I Automatically Select Slicer Options?

Question

I am working on an Excel workbook that contains large amounts of entries in one large table, all the entries are sorted by date. I have inserted a slider for easier viewing of the data by month, but now what I want to do is have the slider options automatically selected.

Eg.

One of my worksheets is designed to show a month summary, all of the totals and such are calculated with a SUMIF based on the date so that only the current month's data is totaled, now on that worksheet i also want to include a slicer with all that months entries, Easy enough, but i want the current month automatically selected, it will confuse people if after selecting monthly statement they receive most of the info automatically but then have to select the month they are after for the other parts of the statement. So what i want is a way to make the slicer default to a value set in another cell.

Answer 1

you can use .Sliceritems(iIndex).selected to define what is selected.

Assuming your labels are dates, this is a way of setting the selections

Sub Macro1()

Dim lIndex As Long
Dim lLoop As Long

    With ActiveWorkbook.SlicerCaches("Slicer_NumEntered")
    lIndex = .SlicerItems.Count
        For lLoop = 1 To lIndex
            If CDate(.SlicerItems(lLoop).Name) < CDate("5/1/2012") Then
                .SlicerItems(lLoop).Selected = True
            Else
                .SlicerItems(lLoop).Selected = False
            End If
        Next
    End With
End Sub

this could be improved - the date test is horrible, and could be vastly improved. Also, if you could get the test to simply return true/false, then you could delete the if/then/else, and put

.SlicerItems(lLoop).Selected = YourTest

instead