Disable Multi-Select in Excel Pivot Table using VBA

Hello

Is there a way, in Excel 2016, to disable the multi-select and select all options in a pivot table filter?  I found the below post from a couple of years ago and I tried using the VBA syntax from this.

https://www.experts-exchange.com/questions/28528630/Disable-Multi-Select-and-Select-All-Options-in-Excel-2007-Pivot-Table.html

Private Sub Worksheet_Change(ByVal Target As Range)
    If PivotTables("GTMSIC").PivotFields("LOB").CurrentPage = "(All)" Then
        Application.Undo
        MsgBox "Select All-LOB instead", vbCritical + vbOKOnly, "Filter Selection" 'optional message
        PivotTables("GTMSic").PivotFields("LOB").EnableMultiplePageItems = False
    End If
End Sub

However, in my worksheet, the message box is put in an infinite loop. Every time I click ok or try to exit out, it pops up again.

Can anyone help me correct this.
Minnie KaurAsked:
Who is Participating?
 
abbas abdullaConnect With a Mentor Commented:
Hi Minnie,

try this
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    If Range("B1").Value = "(Multiple Items)" Then ' Change this to the cell where your pivot filter is located
        Application.Undo
        MsgBox "Select All-LOB instead", vbCritical + vbOKOnly, "Filter Selection" 'optional message
        'PivotTables("GTMSic").PivotFields("LOB").EnableMultiplePageItems = False
    End If
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.