Remove "All" in pivot drop down

**lckjob** · 12-14-2006, 07:36 PM

Hi
Is it possible to revmove "all' option in the pivot table drop down list ?

THX

**VBA Noob** · 12-14-2006, 07:42 PM

Hi,

Not sure what you mean by "All"

If your trying to protect the pivot thislink might help

http://www.contextures.com/xlPivot12.html#Disable

VBA Noob

**lckjob** · 12-14-2006, 07:57 PM

Attached is the print screen what I mean by "All" option. I have a pivot report and don't want user to have option to pick "all" in the drop down list.

THX

**VBA Noob** · 12-14-2006, 08:01 PM

The link I provided should sort you out then

VBA Noob

**lckjob** · 12-14-2006, 08:06 PM

will it allow user to pick other option on the list ? I am new at this, where should I insert the code ?

**Carim** · 12-15-2006, 03:02 AM

Hi,

You can prevent user from selecting "All" as a choice, with an event macro ...

Please Login or Register  to view this content.

Go to the worksheet tab
Right click
Select view code
Copy code

HTH
Carim

**lckjob** · 12-15-2006, 03:02 PM

I am new at this. I got an error message. See attached print screen. What did I do wrong ?

THX

**Carim** · 12-15-2006, 03:06 PM

Hi,

Only two possibilities ...

1. The sheet in which you copy the code does not have a pivot table ...

2. More probably, the pivot table name is wrong
Place your cursor in any cell of the pivot table
Right click
Select Table Options
Adjust the Name to PivotTable1 ...

HTH
Carim

**lckjob** · 12-15-2006, 04:57 PM

I couldn't get it work. I applied the code to the attached sample but can not get it work. Can you try ?

THX

**VBA Noob** · 12-15-2006, 05:41 PM

Hi,

Try this mod on Carim's code

Please Login or Register  to view this content.

VBA Noob

**lckjob** · 12-15-2006, 05:57 PM

I tried it on the attached sample but could not get it to work. Did I do something wrong ?

**VBA Noob** · 12-15-2006, 06:00 PM

Hi,

Code goes into sheet with Pivot. As you added a new pivot it's in the wrong sheet. Select the tab "Sheet2 (2)" and then right click and add code

VBA Noob

**Carim** · 12-16-2006, 07:17 AM

VBA Noob ... Thanks for your help ...

Attached is corrected worksheet ...

HTH
Carim

**VBA Noob** · 12-16-2006, 07:21 AM

Carim,

It didn't work for me until I went into sheet 3.... very strange. Working now on both sheets

VBA Noob

**Carim** · 12-16-2006, 07:28 AM

VBA Noob,

Since it is a worksheet_calculate event with a pivot table, at least with XL2000, it requires either a change in the pivot table for calculate to be triggered, or adding (in any cell within the worksheet) a volatile function such as Now() or Today() and F9 ...again to fire calculate ...

HTH
Carim

**VBA Noob** · 12-16-2006, 07:36 AM

Carim,

I was changing the pivot table which should of triggered the change event.

It didn't update until I changed sheets which I thought was strange

VBA Noob