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
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

Highlighted

@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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
203 Replies