Snasui Excel Forum

Code: Select all

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

Code: Select all

ActiveCell.FormulaR1C1 = "=GetSelectedSlicerItems(""Slicer_TeamID2"")"

Code: Select all

activecell.value = GetSelectedSlicerItems("Slicer_TeamID2")

Code: Select all

stword3 = ActiveCell.Value = GetSelectedSlicerItems("Slicer_รหัส_สาขา")
stword = "รายงานต่อใบอนุญาต สาขา"
stword4 = "ตัวแทน ประกันวินาศภัย"
stword5 = "ประเภทใบอนุญาต"
Range("A9") = stword & " " & stword3 & " " & stword5 & " " & stword4