Forum Discussion

icorte's avatar
icorte
Copper Contributor
May 23, 2019

can you add a Macro Button based on Slicers?

I have a document with 4 connected slicers.  Basically a road/process map on what text to log in salesforce.

 

I want to assign a macro where someone can click a "button" and text will populate in a cell - based on what someone has chosen in the four slicers.  Is this possible?

 

As an alternative, I added a 5th slicer with the actual text I want to populate, but I can't copy and paste the text out of the slicer.  Is this also possible?

 

I am trying to make it easy for my team to log the same text in salesforce. Any advice?

 

Thanks!

excel
Macros and VBA
Slicer

2 Replies

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    May 24, 2019

    icorte 

    and here it is... i'm new here and i don't know how to attach files...

     

    Sub SelectSelectedSlicer()
    Dim sC As SlicerCache
    Dim objItem As Object
    Dim strSelection As String

    Set sC = ActiveWorkbook.SlicerCaches("Slicer_Case_Type1")
    With sC

    For Each objItem In .SlicerItems

    If objItem.Selected = True Then
    strSelection = strSelection & objItem.Caption & ";"
    End If

    Next objItem

    End With

    Set sC = ActiveWorkbook.SlicerCaches("Slicer_Outcome?1")
    With sC

    For Each objItem In .SlicerItems

    If objItem.Selected = True Then
    strSelection = strSelection & objItem.Caption & ";"
    End If

    Next objItem

    End With

    Set sC = ActiveWorkbook.SlicerCaches("Slicer_Detailed_Reason1")
    With sC

    For Each objItem In .SlicerItems

    If objItem.Selected = True Then
    strSelection = strSelection & objItem.Caption & ";"
    End If

    Next objItem
    End With

    Set sC = ActiveWorkbook.SlicerCaches("Slicer_Detailed_Reasons_Description1")
    With sC

    For Each objItem In .SlicerItems

    If objItem.Selected = True Then
    strSelection = strSelection & objItem.Caption & ";"
    End If

    Next objItem

    Sheet1.Range("U7").Value = strSelection

    End With

    End Sub

      

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    May 24, 2019

    icorte 

    Hi,

    i create an example which use slicers to control listobjects. All selected slicers will be written in cell K1

     

    Sub SelectSelectedSlicer()
    Dim sC As SlicerCache
    Dim objItem As Object
    Dim strSelection As String

    Set sC = ActiveWorkbook.SlicerCaches(1)
    With sC

    For Each objItem In .SlicerItems

    If objItem.Selected = True Then
    strSelection = strSelection & objItem.Caption & vbLf
    End If

    Next objItem

    Sheet1.Range("K1").Value = strSelection

    End With

    End Sub

     

    best regards

    Bernd

    http://www.vba-Tanker.com

Resources