[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

show this of pivot - double clikc

Posted on 2013-12-09
6
Medium Priority
?
304 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 93

Expert Comment

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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 …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

650 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