[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3517
  • Last Modified:

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

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
aarick161
Asked:
aarick161
  • 4
  • 3
  • 3
1 Solution
 
ProfessorJimJamCommented:
use  

pTbl.PivotFields("thenameofthefield").EnableItemSelection = False
0
 
ProfessorJimJamCommented:
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
 
aarick161Author Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Glenn RayExcel VBA DeveloperCommented:
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
 
aarick161Author Commented:
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
 
ProfessorJimJamCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
ProfessorJimJamCommented:
great job Glenn
0
 
aarick161Author Commented:
It worked!  Thanks so much.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now