Home

can you add a Macro Button based on Slicers?

%3CLINGO-SUB%20id%3D%22lingo-sub-638681%22%20slang%3D%22en-US%22%3Ecan%20you%20add%20a%20Macro%20Button%20based%20on%20Slicers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638681%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20document%20with%204%20connected%20slicers.%26nbsp%3B%20Basically%20a%20road%2Fprocess%20map%20on%20what%20text%20to%20log%20in%20salesforce.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20assign%20a%20macro%20where%20someone%20can%20click%20a%20%22button%22%20and%20text%20will%20populate%20in%20a%20cell%20-%20based%20on%20what%20someone%20has%20chosen%20in%20the%20four%20slicers.%26nbsp%3B%20%3CSTRONG%3EIs%20this%20possible%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%2C%20I%20added%20a%205th%20slicer%20with%20the%20actual%20text%20I%20want%20to%20populate%2C%20but%20I%20can't%20copy%20and%20paste%20the%20text%20out%20of%20the%20slicer.%26nbsp%3B%20%3CSTRONG%3EIs%20this%20also%20possible%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20it%20easy%20for%20my%20team%20to%20log%20the%20same%20text%20in%20salesforce.%20Any%20advice%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-638681%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESlicer%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643002%22%20slang%3D%22en-US%22%3ERe%3A%20can%20you%20add%20a%20Macro%20Button%20based%20on%20Slicers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347577%22%20target%3D%22_blank%22%3E%40icorte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20create%20an%20example%20which%20use%20slicers%20to%20control%20listobjects.%20All%20selected%20slicers%20will%20be%20written%20in%20cell%20K1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20SelectSelectedSlicer()%3CBR%20%2F%3EDim%20sC%20As%20SlicerCache%3CBR%20%2F%3EDim%20objItem%20As%20Object%3CBR%20%2F%3EDim%20strSelection%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20sC%20%3D%20ActiveWorkbook.SlicerCaches(1)%3CBR%20%2F%3EWith%20sC%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20objItem%20In%20.SlicerItems%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objItem.Selected%20%3D%20True%20Then%3CBR%20%2F%3EstrSelection%20%3D%20strSelection%20%26amp%3B%20objItem.Caption%20%26amp%3B%20vbLf%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20objItem%3CBR%20%2F%3E%3CBR%20%2F%3ESheet1.Range(%22K1%22).Value%20%3D%20strSelection%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644982%22%20slang%3D%22en-US%22%3ERe%3A%20can%20you%20add%20a%20Macro%20Button%20based%20on%20Slicers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644982%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347577%22%20target%3D%22_blank%22%3E%40icorte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20here%20it%20is...%20i'm%20new%20here%20and%20i%20don't%20know%20how%20to%20attach%20files...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20SelectSelectedSlicer()%3CBR%20%2F%3EDim%20sC%20As%20SlicerCache%3CBR%20%2F%3EDim%20objItem%20As%20Object%3CBR%20%2F%3EDim%20strSelection%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20sC%20%3D%20ActiveWorkbook.SlicerCaches(%22Slicer_Case_Type1%22)%3CBR%20%2F%3EWith%20sC%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20objItem%20In%20.SlicerItems%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objItem.Selected%20%3D%20True%20Then%3CBR%20%2F%3EstrSelection%20%3D%20strSelection%20%26amp%3B%20objItem.Caption%20%26amp%3B%20%22%3B%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20objItem%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20sC%20%3D%20ActiveWorkbook.SlicerCaches(%22Slicer_Outcome%3F1%22)%3CBR%20%2F%3EWith%20sC%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20objItem%20In%20.SlicerItems%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objItem.Selected%20%3D%20True%20Then%3CBR%20%2F%3EstrSelection%20%3D%20strSelection%20%26amp%3B%20objItem.Caption%20%26amp%3B%20%22%3B%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20objItem%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20sC%20%3D%20ActiveWorkbook.SlicerCaches(%22Slicer_Detailed_Reason1%22)%3CBR%20%2F%3EWith%20sC%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20objItem%20In%20.SlicerItems%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objItem.Selected%20%3D%20True%20Then%3CBR%20%2F%3EstrSelection%20%3D%20strSelection%20%26amp%3B%20objItem.Caption%20%26amp%3B%20%22%3B%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20objItem%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20sC%20%3D%20ActiveWorkbook.SlicerCaches(%22Slicer_Detailed_Reasons_Description1%22)%3CBR%20%2F%3EWith%20sC%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20objItem%20In%20.SlicerItems%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20objItem.Selected%20%3D%20True%20Then%3CBR%20%2F%3EstrSelection%20%3D%20strSelection%20%26amp%3B%20objItem.Caption%20%26amp%3B%20%22%3B%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20objItem%3CBR%20%2F%3E%3CBR%20%2F%3ESheet1.Range(%22U7%22).Value%20%3D%20strSelection%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
icorte
Regular Visitor

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!

2 Replies

@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

www.vba-Tanker.com

@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

  

Related Conversations