Solved

show this of pivot - double clikc

Posted on 2013-12-09
6
295 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 AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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