Pivot Table Page Field #2

Hi,

Does anyone know if I can interact with the (All) selection of a pivot table 
page field?  I would like to either take it out or change it's caption.

Neil


0
Neily (2)
2/10/2005 9:11:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
152 Views

Similar Articles

[PageSpeed] 24

You can't suppress the "All" option in the page field, or change its 
caption. With programming, you could select another item if the user 
selects "All".

For example:
'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Set pt = Me.PivotTables(1)
Set pf = pt.PivotFields("Region")

With pf
    If .CurrentPage = "(All)" Then
        .CurrentPage = .PivotItems(1).Name
    End If
End With
End Sub
'============================

This code is stored on the worksheet's code module --
    Right-click the sheet tab, and choose View Code
    Paste the code where the cursor is flashing.


Neily wrote:
> Hi,
> 
> Does anyone know if I can interact with the (All) selection of a pivot table 
> page field?  I would like to either take it out or change it's caption.
> 
> Neil
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/10/2005 1:02:21 PM
Hi Debra,

I suspected you couldn't actually mess about with the (All) field, but this 
will be really helpful,

Thanks

"Debra Dalgleish" wrote:

> You can't suppress the "All" option in the page field, or change its 
> caption. With programming, you could select another item if the user 
> selects "All".
> 
> For example:
> '========================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim pt As PivotTable
> Dim pf As PivotField
> Set pt = Me.PivotTables(1)
> Set pf = pt.PivotFields("Region")
> 
> With pf
>     If .CurrentPage = "(All)" Then
>         .CurrentPage = .PivotItems(1).Name
>     End If
> End With
> End Sub
> '============================
> 
> This code is stored on the worksheet's code module --
>     Right-click the sheet tab, and choose View Code
>     Paste the code where the cursor is flashing.
> 
> 
> Neily wrote:
> > Hi,
> > 
> > Does anyone know if I can interact with the (All) selection of a pivot table 
> > page field?  I would like to either take it out or change it's caption.
> > 
> > Neil
> > 
> > 
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
Neily (2)
2/24/2005 10:45:01 AM
You're welcome. Thanks for letting me know that it helps.

Neily wrote:
> Hi Debra,
> 
> I suspected you couldn't actually mess about with the (All) field, but this 
> will be really helpful,
> 
> Thanks
> 
> "Debra Dalgleish" wrote:
> 
> 
>>You can't suppress the "All" option in the page field, or change its 
>>caption. With programming, you could select another item if the user 
>>selects "All".
>>
>>For example:
>>'========================
>>Private Sub Worksheet_Change(ByVal Target As Range)
>>Dim pt As PivotTable
>>Dim pf As PivotField
>>Set pt = Me.PivotTables(1)
>>Set pf = pt.PivotFields("Region")
>>
>>With pf
>>    If .CurrentPage = "(All)" Then
>>        .CurrentPage = .PivotItems(1).Name
>>    End If
>>End With
>>End Sub
>>'============================
>>
>>This code is stored on the worksheet's code module --
>>    Right-click the sheet tab, and choose View Code
>>    Paste the code where the cursor is flashing.
>>
>>
>>Neily wrote:
>>
>>>Hi,
>>>
>>>Does anyone know if I can interact with the (All) selection of a pivot table 
>>>page field?  I would like to either take it out or change it's caption.
>>>
>>>Neil
>>>
>>>
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
2/24/2005 1:23:41 PM
Reply:

Similar Artilces:

Install #2
I just installed GP10 on a users Windows 7 machine. I went through the process, then changed the dynamics.set file to report to the reports.dic file on the server as we are connecting to a remote server. When I open GP it says I must use utilities first. Fine, GP re-opens in utilities. Then it gives me the option to "Update modified forms and reports? which I do, it points to the local reports.dic, which is odd, but ok. when I click process it errors out saying "reports.dic: unable to open. Any ideas??? Thanks, Matt ...

TO field blank in Outlook 2003 intermittently
Other people have had this problem, but I've yet to find a solution. When sending new messages, replying to messages, etc. Outlook 2003 users will send messages that are missing the TO field information on receipt. If they check Sent Items, the TO fields are also blank. Sometimes if sending to three people, one will show or none. No rhyme or reason to it. Using Exchange 5.5 SP4, btw, which seems to be a common theme. Will this happen when you use Outlook 2003 on a stand alone computer. Check out if this happens only when you have Outlook in an exchange server environment. If t...

send/receive settings #2
My co-worker and I are both having a problem with setting the leave a copy on the server. We click the box for saving and choose the amount of days to leave on the server. Then when we close outlook and reopen it is gone. The box is no longer checked to save email on the server. What's happening?? Is there a fix for this problem? ...

sent email #2
I am using outlook 2000 with XP and IE7. When I send email it goes through the outbox and then to sent items HOWEVER the people never receive it. Any suggestions? I am currently including myself in all outgoing email to check the problem. hairbychemo wrote: > I am using outlook 2000 with XP and IE7. > > When I send email it goes through the outbox and then to sent items > HOWEVER > the people never receive it. > > Any suggestions? > > I am currently including myself in all outgoing email to check the > problem. Do you get the mail? Basically, once a messa...

Changing case #2
Is there an easy way to change the case of a block of cells containing data from lower case to upper case? If you want to change the case of cell A1 to uppercase, use the following equation: =upper(A1) "Peter Downes" <pjtdownes@hotmail.com> wrote in message news:exy7jcPcFHA.228@TK2MSFTNGP12.phx.gbl... > Is there an easy way to change the case of a block of cells containing data > from lower case to upper case? > > A1: abc B1: =UPPER(A1) ---> ABC C1: =PROPER(A1) ---> Abc HTH Ola -- olasa -------------------------------------------------------------...

2 cash drawers, one closing ????
We have a POS computer with 2 cash drawers, since its two cashiers that works the station at the same shift. My question is, is there a way to have 2 closing? one per cash drawer / cashiers? This sins since both cash drawers are in the same shift and both start with a petty cash. Whats the recommended way to work a shift with 2 cash drawers/cashiers and to properly square off the shift? ...

Exchange 5.5 & W2k SP4 #2
Hi, Can anybody tell me if there are known problems between Exchange 5.5 sp4 and Windows2000 server sp4. thnx Michiel Runs just fine here. See http://support.microsoft.com/default.aspx?scid=kb;en-us;821543 MightyM wrote: > Hi, > > Can anybody tell me if there are known problems between Exchange 5.5 sp4 > and Windows2000 server sp4. > > thnx > > Michiel -- Help fight spam - designate sending servers for your domain. http://spf.pobox.com ...

Dynamic range #2
In C2:C65 I have dates as follows (DK standard): 01-01-01 01-02-01 01-03-01 etc. indicating January 2001, February 2001, March 2001 and so on up to 01-04-06 (indicating April 2006) in cell C65. In about a fortnight new data will appear i C66: (01-05-06) and later on C67: (01-06-06) etc. I know how to create a dynamic range if I always had to start from the top, but here I want to be able to enter for example 01-01-05 in cell H1 and the dynamic range should now be, not from the top, but from the cell C51 (which holds 01-01-05) and as far down as I (just now) have data. Hans Knudsen I fou...

CRM 3.0 Installation help...2 errors
Going through the system requirements part of the setup I get a Warning on the IIS part. It cant access the URL-- Error accessing URL http://IRONWOOD-CRM: The remote server returned an error: (500) Internal Server Error. The second error I get is : Setup failed to validate specified Reporting Services Report Server http://ironwood-crm/ReportServer. Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. Chris Holub MCP CCNA 1. Check your IIS and default CRM virtual directory is there issue accecing, can you send me detail or s...

Only show one name of DL in To Field
I would like to use a distribution list and when the recipients receive the message have them only see their name in the To field and no one elses. Is this possible. Thanks Have you tried just putting the distribution list in the Bcc (blind carbon copy) field. If it isn't showing click View > Bcc field "Ann" <trotto@beckercollege.edu> wrote in message news:123e01c3c3f4$ba55fd60$3101280a@phx.gbl... > I would like to use a distribution list and when the > recipients receive the message have them only see their > name in the To field and no one elses. Is this...

Microsoft Excel #2
Why can't I close my Excel application by clicking the x button in the upper right hand corner of the application. This is Excel 2002. Is there a way to do this? --- Message posted from http://www.ExcelForum.com/ Seum Is the "x" button grayed out and unavailable? Are using using Groupwise? If that's the problem. See this MSKB article http://support.microsoft.com/default.aspx?scid=kb;en-us;260199 Article is specific for 2000 but I think it is valid for other versions. It gives a workaround (use the file menu) and instructions as to how to remove the Groupwise add-i...

count based on another field
I have a table in my database which records when a case is marked by an assessor. The table shows the case number, the assessor ID that the case has been assigned to and also an indicator to determine whether or not the case is closed. I need the query to show: The assessor ID number The number of cases assigned to a particular assessor the number of cases assigned to a particular assessor that are incomplete The first two are fine, however, how do I limit the last part of the query to show only the cases assigned to each assessor? thanks <barrynichols@gmail.com> wrote in message...

Drawing Graphs #2
Hello, I need to write an application that displays multiple graphs on multiple tabbed sheets in a single window. The graphs are all simple X-Y line graphs like you'd see on an oscilloscope, but with labeled coordinates. I need everything (including the fonts if possible) to scale as I resize the window. I'm debating about using MFC or C#. Is there some standard class or set of functions that supports such graphs or do I need to write my own from the ground up (or buy a 3rd party package)? We've already done this in Java but I'd like to get rid of the Java and r...

DATA DUMP WARNING #2
Maybe the ultimate lesson in this is backup backup backup. Do backups early and often. and use a large cycle - don't write a backup onto the immediately previous media! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respond to any emails that I have not specifically asked for. <DrWoodardOnDS@hotmail.com> wrote in message news:11095315...

Search Performance & Indexing
Hi there, Does anyone know what the search performance is like with a few hundred thousand records on a custom 'account number' field? The out-of-box deployment indexes a few fields, and I am assuming it is unsupported to change these indexes. I have read through the performance papers from Microsoft and they have benchmark tested against few million records without any issues - but primarily these searches were done on out-of box fields. Any thoughts and advise on this would be greatly appreciated. ...

email page layout
Somehow the Windows Live Mail page layout changed on my laptop. Instead of the top portion of the page having the senders address and subject on the top and the message on the bottom, I now have the data in three columns: inbox, send, etc senders address and subject message How can I change the layout back to the original? View (ALT+V), Layout, Reading Pane -- Mike - http://TechHelp.Santovec.us "mcalbud" <mcalbud@discussions.microsoft.com> wrote in message news:2356B046-BE39-4222-A1B2-7C9F77F6FF83@microsoft.com... > Somehow the Windows Live Mail ...

Page icons in Publisher 2000 have disappeared.
My page number icons have disappeared. Now I have to click on "go to page __" to move from page to page. How do I get those icons back? View, Toolbars, Status bar... -- Mary Sauer http://msauer.mvps.org/ "GabPub" <GabPub@discussions.microsoft.com> wrote in message news:ED718A66-1F8C-4F59-93DA-71FC7B4CE2E8@microsoft.com... > My page number icons have disappeared. Now I have to click on "go to page __" > to move from page to page. How do I get those icons back? Select View, Tool Bars, Status Bar. That should bring back the page icons. -- Do...

Error message with CAPICOM 2.1.0.2 Not enough storage is available
Hi everybody, I need some help. With CAPICOM 2.1.0.1, when I try to store certificates from a smart card I use Store := CoStore.Create; Cert:=CreateComObject(CLASS_Certificate) as ICertificate2; Store.Open(CAPICOM_SMART_CARD_USER_STORE, 'MY',CAPICOM_STORE_OPEN_READ_ONLY); without any problem. I have updated with CAPICOM 2.1.0.2, and the same code produces an error message:"Not enough storage is available to complete this operation" when store.open. I need use this library. My os is Microsoft Windows XP Professional Version 2002. SP3. My Internet e...

I have MS Office XP Prof. & need System Office pack 2.0 or later
I just recently installed the Windows 7 Operating System on my computer. I use Microsoft Office XP Professional as my office software. While editing a document I received the following message: " The Office Assistant requires Microsoft Agent 2.0 or later. This product is available on the Office System Pack." Where can I get this? The link to request a hotfix is here: <http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=969168&kbln=en-us> The full article is here: <http://support.microsoft.com/kb/969168> hth Sabrina wrote: > &g...

Org Chart:1 Manager 2 Sections
How do I divide two work sections of employees under one manager? I tried arranging the first set of staff under a vertical alignment and wrapping a team frame around them, but when I add positions to create the 2nd team, they are aligned the same. If I arrange the employees horizontally, members are randomly assigned to the left or right, breaking the team arrangments. ...

Need help setting up fields on a liquor store import file.
What is the best way to set up the fields on the excel sheet (import) for a liquor store. Should I put the size of the bottle with the extended description or should i put the size in the sub description? I also have sku's (item lookup codes) for each child item but do not have one for the parent (a case of the product). How should I handle this? Do I make up my own lookup codes or should i just not use the parent child relationship and store everything as a child? What benefits does each one have? I always include the size in the primary description field. I always keep the d...

table of contents in excel
Does anybody know how to create a dynamic table of contents in Excel (similar to Words TOC). Or alternatively how to insert the current page number into a cell so that it can be cross-referenced. Thanks Nick Hi Nick, See David McRitchies TOC page: http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- Regards, Norman "nickH" <nickH@discussions.microsoft.com> wrote in message news:28FEEDC1-6091-4FC4-BD5C-5CE2A6116A5E@microsoft.com... > Does anybody know how to create a dynamic table of contents in Excel > (similar > to Words TOC). Or alternatively ho...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

unable to read file #2
For some reason all of a sudden I am trying to open a Workbook and I am getting an "unable to read file". This is a workbook that I have opened before. It is a workbook that I "Save AS" and change the name with new data on a weekly basis so I have data for every week since april. I can open every week but for some reason can not open this one. Any suggestions as to what I can do? thanks Try 'www.fdrlab.com' (http://www.fdrlab.com/repair.html) They can recover corrupt workbooks with all contained information including comments, VBA, charts, links etc. 35$ ...

Decimal places in a temporary table
I am using a temporary table to perform calculations before appending the resultant invoice transaction records to my main table. The temporary table is created in code like this: strSQL = "CREATE TABLE tblInvoiceTransactionsTemp (fldInvoiceTransactionID Double,fldProductID Double, fldIFAID Double,fldQuantity Integer, fldFreeOfCharge YesNo," strSQL = strSQL & "fldEnqID Double, fldInvoiceHeaderID Double, fldAgreedPrice Currency, fldVATRate Double)" DoCmd.RunSQL strSQL This all works fine but the problem is the VAT (tax) rate - the current rate is 17.5% but wh...