Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Failure of PivotSelection Property (Excel 9, 10 &

    I am migrating several Excel 97 applications.

    I have encountered a strange error.

    The source data is an Excel database.
    The pivot table has been created manually and in VBA.
    Pivot fields: RowFields = 1, ColumnFields = 1, DataFields = 1, AreaFields = 0.
    The pivot table Selection Mode setting has no effect.


    In Excel 8, the PivotSelection Property works as described in Help.

    Sheets(PivotSheetName).PivotTables(PivotTableName) .PivotSelection "'Field 1' 'Field 2'"

    StrVarA = Sheets(PivotSheetName).PivotTables(PivotTableName) .PivotSelection


    In Excel 9, 10 & 11, the PivotSelection Property does not work as described in Help. The PivotSelection Property fails to return a value with the Run-time error '1004'.

    This works:

    Sheets(PivotSheetName).PivotTables(PivotTableName) .PivotSelection "'Field 1' 'Field 2'"

    Note: using this line to select a pivot data cell highlights the row and column titles where
    selecting the same cell with keyboard or mouse does not.

    This Fails:

    StrVarA = Sheets(PivotSheetName).PivotTables(PivotTableName) .PivotSelection

    Unless:
    If this line is executed immediately after the selection line is executed, this line works.

    I can code around this but it is a pain in the fingers, especially to accommodate multiple row, column, data, and area fields!

    Any ideas?
    MJB

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Failure of PivotSelection Property (Excel 9, 10 &

    Welcome to Woody's Lounge!

    You can use the PivotSelect method instead of the PivotSelection property - this will let you specify what exactly you select, e.g.
    <code>
    Sheets(PivotSheetName).PivotTables(PivotTableName) .PivotSelect "'Field 1' 'Field 2'", xlDataOnly
    </code>
    Does the code between setting thePivotSelection and trying to retrieve it change the selection within the pivot table? That could cause the retrieval to fail.

  3. #3
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi Hans,

    Thanks for the response.

    The problem is not with selecting a pivot cell. The problem with .PivotSelection is returning the currently selected pivot cell.

    Both .PivotSelect and .PivotSelection will select a pivot cell. And if you use either of these methods, then use .PivotSelection to return the pivot cell selected, the correct pivot cell reference is returned.

    However, if you use the keyboard or mouse to select a pivot cell, then use .PivotSelection to return the pivot cell selected, Excel returns a 1004 error.

    It is interesting to note that when code is used to select a pivot cell the pivot row and column titles are highlighted. When the keyboard or mouse is used to select a pivot cell the row and column titles are not highlighted.

    The knowledge base is silent on this situation.

    MJB

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    If you use PivotSelect with xlDataOnly as second argument, only the data cell is selected, not the row and column titles. If you omit the second argument, the default xlDataAndLabel is used. (I tried to indicate this implicitly in my previous reply, but that was too subtle)

    If you want to use PivotSelection to obtain the cell selected by the user, turn PivotTableSelection off, then on again:

    Application.PivotTableSelection = False
    Application.PivotTableSelection = True
    Debug.Print ActiveSheet.PivotTables(1).PivotSelection

  5. #5
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi hans,

    Thanks.

    I had tried setting Application.PivotTableSelection = True but not the False then True reset.

    MJB

  6. #6
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi Hans,

    The solution:

    Application.PivotTableSelection = False
    Application.PivotTableSelection = True
    Debug.Print ActiveSheet.PivotTables(1).PivotSelection

    works for a single cell selection but not for a range selection.

    For example, if a user double clicks on a pivot cell to drill down, the solution returns the pivotselection for the cell so the drilldown can be intercepted and formatted.

    However, if the user selects any range that is more than a single cell (continuous or not), the solution fails to return the range.

    Application.PivotTableSelection = False changes the selection to the active cell and the range is lost

    How can a pivottable be setup so that the user can make keyboard or mouse selections directly in the pivottable (as in Excel 97) so that the .PivotSelection function works properly (according to online help and as it did in Excel 97)?

    The applications that I am converting are used by Excel minimalists. They have no time or patience for pop-up menus and formatting. They want to click and see, period. So all of the advanced user functionality must be in VBA.

    Thanks,
    MJB

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    If I move the mouse pointer just to the left of row items or just above column items, it changes to a solid horizontal or vertical arrow. I can then click to select a single item, Shift+click or click and drag to select multiple contiguous items, or Ctrl+click to select non-contiguous items (they can be in different fields, and both in row fields and colum fields). PivotSelection correctly returns these selections (without toggling PivotTableSelection).

    I don't know of a way to use PivotSelection to handle an arbitrary selection of cells in the pivot table.

  8. #8
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi Hans,

    Thanks, that works perfectly.

    I will have to write a function to return the range pivot addressing.

    That won't be nearly as difficult as retraining the users to notice the small arrows.

    Thanks again,
    MJB

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    I'm still not sure exactly what you are trying to do, but have you looked at the <code>Range.PivotCell</code> property?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi Rory,

    Sorry about the delay answering. I don't check the e-mail regularly.

    Among other things, I must provide selection of both continuous and discontinuous pivot table row and column ranges for drilldown and for charting.

    I also must implement double-clicking on a row to show and hide nested detail.
    For example: days within months within years.
    Analysis Date
    Year | Month | Day
    2007 | May | May-01

    My users are interested in the finished information, and are not sympathetic to having to do more than click a button or menu item, or right-click or double-click to get it. So everything must be done with VBA.

    They also want it in a form that they are familure with. So Excel is the choice.

    I am currently developing a function to return a .PivotSelection formatted pivot table selection made on the worksheet rather than the overlayed pivot table.
    I an using the Range.PivotCell property and the various Activecell.PivotTable address range properties and the Selection.Address property.
    Unfortunately, the way interruptions pop up, I haven't got it finished yet.

    Hope this gives you some idea of what I am upto,
    MJB

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Ah yes, the age-old problem: "we want this in a form we are familiar with so it has to be in Excel, only we don't know how to use Excel properly so you will have to make it idiot proof" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Perhaps an Excel training course focussed on Pivot Tables might be in order??
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    May 2007
    Location
    Spokane, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    Hi Rory,

    A-a-a-a-a-h sympathy. How could we survive our worlds of escillatingly ignorant and outragous demands without it <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

    Thanks,
    MJB <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Failure of PivotSelection Property (Excel 9, 1

    For what it's worth, I suspect most of the issues you are having are probably related in some way to the changes that occurred with the GETPIVOTDATA function through various versions of Excel.
    I still say that a judicious slap or two to the right users is worth several hours of programming... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •