• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

show this of pivot - double clikc

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
Rayne
Asked:
Rayne
  • 4
  • 2
1 Solution
 
RayneAuthor Commented:
Sp, the workbook, worksheets will remain protected to all users
0
 
RayneAuthor Commented:
so how can the double click to show details of pivot  - will work on a protected workbook and sheet?
0
 
RayneAuthor Commented:
or what other ways i can improvise to get around this problem?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Patrick MatthewsCommented:
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
 
RayneAuthor Commented:
Thank you Patrick :)
This is brilliant, never came to my head - you are best in the league - All Hats off

Respect,
Rayne
0
 
Patrick MatthewsCommented:
Glad to help :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now