Solved

show this of pivot - double clikc

Posted on 2013-12-09
6
292 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now