Solved

Disable Multi-Select and Select All Options in Excel 2007 Pivot Table

Posted on 2014-09-30
10
1,981 Views
Last Modified: 2014-10-03
Is there a way, in Excel 2007, to disable  the multi-select and select all options in a pivot table filter?  Doesn't matter if it requires VBA code or not.  

The pivot tables in question are using MAX calculations, so if the user selects more than one item in the pivot filter, the results will not come back correctly.  I do not expect that the users will follow instructions and will attempt to use the multi-select/select all options.
0
Comment
Question by:aarick161
  • 4
  • 3
  • 3
10 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353168
use  

pTbl.PivotFields("thenameofthefield").EnableItemSelection = False
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353191
Sub DisableMonthSelection()
  
Dim pt As PivotTable

  
For Each pt in ActiveSheet.PivotTables

 pt.PivotFields("yourfieldname").EnableItemSelection = False
Next pt
End Sub

Open in new window

0
 

Author Comment

by:aarick161
ID: 40353331
ProfessorJimJam - The code you provided did remove the select all and multiselect options.  Unfortunately it also disabled the filter as whole leaving the user unable to select even a single item.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353343
I think you still want the user to be able to select a filter item, but only one and never (All).  

You could add this VBA routine to the sheet object where the PivotTable resides:
Private Sub Worksheet_Change(ByVal Target As Range)
    If PivotTables(PTname).PivotFields(ReportFilterfield).CurrentPage = "(All)" Then
        Application.Undo
        'Msgbox "Please choose only one item",vbcritical+vbOKOnly,"Filter Selection" 'optional message
        PivotTables(PTname).PivotFields(ReportFilterfield).EnableMultiplePageItems = False
    End If
End Sub

Open in new window

where PTName is the PivotTable name (put in quotes), and ReportFilterfield is the name of the field (also, put in quotes).

There is also an optional message box you could display (commented out in this example)

Regards,
-Glenn
0
 

Author Comment

by:aarick161
ID: 40353396
Glenn - I tried your code, but to no effect.  Perhaps you can upload a 2007 workbook with your code in action so I can see if I'm doing something wrong?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353404
Glenn  

if your code would work. it would be brilliant

I modified it a bit but still the debugger stops at the first line
Private Sub Worksheet_Change(ByVal Target As Range)
    If PivotTables("PivotTable1").PivotFields("ReportFilterfield").CurrentPage = "(Select All)" Or PivotTables("PivotTable1").PivotFields("ReportFilterfield").EnableMultiplePageItems = True Then
        Application.Undo
        'Msgbox "Please choose only one item",vbcritical+vbOKOnly,"Filter Selection" 'optional message
        PivotTables("PivotTable1").PivotFields("ReportFilterfield").EnableMultiplePageItems = False
    End If
End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353440
I tested this on a proprietary dataset...let me whip up a sample set and PivotTable so you can see.  It worked fine for me; you just have to plug in the correct PivotTable name and fieldname.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40353463
Here you go:  This has a Report Filter called "AdmitDate" and is saved with a single date already.  If you try to choose (All) or try to select multiple items, it will undo the action and report an warning message.

Regards,
-Glenn
EE-PivotTable-RestrictFilter.xlsm
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353473
great job Glenn
0
 

Author Closing Comment

by:aarick161
ID: 40358835
It worked!  Thanks so much.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now