Double Click to Source Data in a protected sheet

**B16NKO** · 12-15-2010, 07:21 AM

All,

First post so bear with me...

I'm looking to improve security in an internal spreadsheet and need some help understanding the best way to solve my problem.

I've got a central data collection sheet that references certain fields in around 33 subsequent sheets in the same workbook.

I've been looking at using:

Please Login or Register  to view this content.

Which is protecting the sheet correctly...My problem arises when I want to perform a double click on a cell to take me to the source data that is referenced in that cell.

I'm unsure what code to use to unprotect the sheet only at the time of the double click to enable the source data sheet to be displayed, whilst protecting the template sheet afterwards.

Look forward to your replies.

NKO

**royUK** · 12-15-2010, 08:04 AM

Why double click, can't you just use hyperlinks?

Also, please read the forum rules, you should use Code Tags when posting code. I've added them for you

**B16NKO** · 12-15-2010, 08:15 AM

Thanks for the reply roy,

The main data collection sheet has 473 lines of data in for the last 7 months so it contains a large amount of info, to hyperlink to a sheet for each cell holding info isn't going to be feasible.

Also, by double clicking through a given cell it will allow the user to be directed exactly to the information that is referenced in the particular cell they have clicked on.

I appreciate that a hyperlink would have been the easiest way of acheiving this had I not needed the specifically referenced cell to be highlighted for such a large amount of data.

Also apologies for the Code tag!

NKO

**royUK** · 12-15-2010, 08:39 AM

You can only have one Double_Click event, so you need to build a check into the code to determine if & where to link to.

**B16NKO** · 12-15-2010, 09:01 AM

OK, so code a check in to say link to data contained in the formula in the cell?

Could you perhaps give me an example?

Thanks

NKO

**royUK** · 12-15-2010, 09:14 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Are you basically looking to "drill down"? If so a PivotTable is probably the best solution

Doing this will ensure you get the result you need!

**B16NKO** · 12-15-2010, 09:44 AM

Sorry Roy, I think there's a bit of confusion, let me try and simplify as I'm unable to provide an example workbook.

The data collection sheet has cells containing formulae, linked to cells contained in other sheets in the same workbook.

By unticking the Edit directly in cell box, under Tools>Options>Edit you can click through cells to (as you say) "Drill-Down" to the source data that the cells in the collection sheet are pointing at.

The issue is around governance, the formulae in the data collection sheet have to be locked so that the data cannot be changed, however we still need the functionality to drill-down on the cells contained in the collection sheet to analyse where the info is coming from.

A pivot table wont allow us to do this as the collection template needs to remain in the same format due to down stream dependencies.