locked
How to disable Multi-selection thing in slicer. RRS feed

  • Question

  • Hello,

    I am using Slicer in Excel Sheet and i want to disable Multi-selection thing in slicer.

    Is it possible to throw an exception or doesn't allow to do a multiple selection in excel slicer using VBA or without?

    Quick help ll be appreciated  :)

    Thanks & regards,

    Gaurav Badhani.


    Wednesday, April 9, 2014 3:52 PM

Answers

All replies

  • This can be done in the DAX measures themselves.

    For example, if the pivot you are using has a measure called Total, you could wrap it in the following DAX pattern so that if more than one slider option is selected, the measure returns no value:

    New Measure:=
    
    IF(HASONEVALUE(SlicerTableName[SlicerColumnName]),
      [Total],
      BLANK()
    )

    Now use New Measure in your pivot in place of Total and multi-selects on that slicer will result in blanks for the measure.

    You could use another value besides BLANK() if it was more appropriate for your needs.

    This must be done for each measure in the pivot.

    There are some variations depending on your specific needs but this is the basic pattern.

    Wednesday, April 9, 2014 5:00 PM
    Answerer
  • Thanks for your reply,

    I used below given code and it's working and i put this code on ThisWorkbook and calling on

     Workbook_SheetCalculate

    Workbook_SheetSelectionChange

    it's working fine but whenever i am selecting slicer value using CTRL key this code is not excuting coz system is not able to call events

    Kindly help me with the events or code coz i am using CTRL key for multi selection and whenever i am using CLRL key it's not working

    Please find below given code

     Dim scl As SlicerCacheLevel
        Dim oSlicer As Slicer
        Dim i As SlicerCaches
        Set i = ActiveWorkbook.SlicerCaches
        Dim oSi As SlicerItem

        Dim ItemsAllowed As Integer
        Dim cntItems As Integer

        ItemsAllowed = 1
        cntItems = 0

        Application.EnableEvents = False

        For Each oSi In ActiveWorkbook.SlicerCaches("Slicer_RC_Description").SlicerItems

             
             If oSi.Selected Then cntItems = cntItems + 1
            If cntItems > ItemsAllowed And oSi.Selected Then
                oSi.Selected = False
                MsgBox "Please select only one item"
                Application.EnableEvents = True
                Exit Sub
            End If
        Next oSi

    ''''''''''''''

    Also tell me what event i should use for ur code or how can i use code using CTRL key

    Thanks & Regards,

    Gaurav Badhani

    A

    Wednesday, April 9, 2014 5:16 PM
  • You might try Worksheet_PivotTableUpdate.

    Someone else may be able to give you more info on executing this with VBA.

    I normally try to avoid VBA in my Power Pivot solutions because most of them ultimately end up on SharePoint and Excel Services does not do VBA.

    Wednesday, April 9, 2014 5:26 PM
    Answerer
  • Hi Gaurav,

    It's seems we can achieve this requirement via VBA code(Not very sure, and I'm not a VBA coding expert). So, I suggest you open a thread in the forum below:
    Microsoft Office Excel IT Pro Discussions: http://social.technet.microsoft.com/Forums/en-US/home?forum=excel

    In addition, I google an article regarding Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA for your reference. Please see: http://www.databison.com/slicer-vba-code-create-change-or-modify-a-pivot-table-slicer-using-vba/

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, April 15, 2014 2:43 AM