Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Slicer option disabled - XL2010

    I have a workbook that upon initialization pulls data from an Access database and then creates dynamically (VBA) a pivottable with that data. The application runs fine - no problem there. But when I tried to introduce Slicers, a 2010 feature, I find that no matter what I do, the option Insert Slicer on the Pivottable Tools tab remains stubbornly disabled.

    There is a case on the web where this happened if the workbook was in the old 2003 format. But as far as I can tell my workbook is saved as an Excel (macro-enabled) Workbook.

  2. #2
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Just a thought, is the workbook or ranges in the pivot table protected? Or perhaps the VBA sets a status?
    Paul Coyle
    Approach love and cooking with reckless abandon

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Nope, no protection. What do you mean by "VBA sets a status"?

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    The VBA that takes the data from Access into Excel may then set say protected ranges in the spreadsheet.
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Does this link help with the problem?

    http://social.answers.microsoft.com/...e-4339d60f68b9
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    The file is in the xlsm format, because it has an embedded macro. Nevertheless, I resaved it under a different name and subsequently reopened it, but to no avail. The option stays grayed out. The macro itself is no big deal: it pulls data from access and creates the pivottable. Once the data is pulled into a recordset, the creation of the pivottable is straightforward. No special options are used.

    It remains a mystery to me. I've nowhere read that you can't apply slicers to a pivottable created with VBA. I'm baffled but not desperate: after all the application works.

  7. #7
    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
    I suspect the issue is that your pivot table doesn't have a data connection, since you assigned it a disconnected recordset.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Rory, why should that be the case? If I enter the data manually into a spreadsheet range, then there isn't even a dataset and I still can create a pivottable.

  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
    If you use a range, there is a connection between the pivotcache and the range (since you can alter the data and refresh the table). If you assign a recordset, there is no connection (and you can't refresh the data). Why that should necessarily remove the slicer ability I don't know, but I suspect that it is the reason.
    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
  •