Solved

show this of pivot - double clikc

Posted on 2013-12-09
6
298 Views
Last Modified: 2013-12-11
Hello

When I click on anywhere column B of the pivot – double click on B5 , it shows the details of the pivot in another sheet – Now the requirement is that the pivot  and the sheet needs to be protected EXCEPT for cells B4:B15 – these cells can remain unprotected
So what needs to happen in VBA so that even if the sheet and pivot is protected – the users on double-clicking the cells B4:B15 – can still see the details of the pivot (selection.showdetails)
Worksheet code event or something else….please guide
Thanks
pivot-show-detail.xlsm
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:Rayne
ID: 39707528
Sp, the workbook, worksheets will remain protected to all users
0
 

Author Comment

by:Rayne
ID: 39707530
so how can the double click to show details of pivot  - will work on a protected workbook and sheet?
0
 

Author Comment

by:Rayne
ID: 39707547
or what other ways i can improvise to get around this problem?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39709548
In a regular module, put this code:

Sub ReProtect()
    
    ThisWorkbook.Worksheets("Sheet4").Protect
    
End Sub

Open in new window


Now, in the Sheet4 module, put this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim pt As PivotTable
    Dim ResultRng As Range
    
    Set pt = Me.PivotTables(1)
    Set ResultRng = pt.DataBodyRange
    
    If Not Intersect(ResultRng, Target) Is Nothing Then
        Me.Unprotect
    End If
    
    Application.OnTime Now + TimeSerial(0, 0, 5), "'" & ThisWorkbook.Name & "'!ReProtect"
    
End Sub

Open in new window


That traps the doubleclick before it runs afoul of protection, turns off protection to allow the drilldown, and then schedules the ReProtect sub to run 5 seconds later.
0
 

Author Comment

by:Rayne
ID: 39710601
Thank you Patrick :)
This is brilliant, never came to my head - you are best in the league - All Hats off

Respect,
Rayne
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39712416
Glad to help :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question