Restrict external linking

[danelskibr]

Hello,

I have a protected workbook that contains a hidden and protected sheet. I want to restrict all access to this sheet for the user of the spreadsheet.

The problem I am running into is this: the user can simply link to the cells in the hidden sheet from a different workbook. This essentially allows them to recreate the entire contents of the hidden and protected sheet.

Here is an example of what I am talking about:
='[ProtectedWorkbook.xlsm]HiddenSheet'!A1

Is there a way I can prevent linking to this sheet by a different workbook? I need to link to it within the same workbook, so I am hoping I can restrict access only to external workbooks.

Thank you in advance for the help!

 

[DILIPandey]

the user can simply link to the cells in the hidden sheet from a different workbook.

How will that happen if user don't know the name of hidden sheet ?


Regards,
DILIPandey

 

[Scott T]

How will that happen if user don't know the name of hidden sheet ?


Regards,
DILIPandey

The OP has formula that use data on the hidden sheet so a user could know the name of the sheet by looking in the formula bar as it will show the sheet reference.

 

[danelskibr]

Scott T is correct. The formulas on the non-hidden sheets reference the data on the hidden sheet.

My temporary solution is to set the cell protection to "Hidden" so the users cannot see the formulas in the cells. However, this is not ideal. I would like the users to be able to see what the formulas are doing. I just do not want them to be able to recreate the data from the hidden sheet.

I feel like I must be missing something.

 

[DILIPandey]

Thanks for the heads-up Scott.
Hi Danelskibr, if you do not want user to use the hidden sheet's reference in the formula then you can take the help of VBA (macros). Below code in ThisWorkbook can help:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Formula Like "*abc*" Then
Target.Clear
End If
End Sub

where abc is the sheet name which you want to restrict.

Regards,
DILIPandey

 

[danelskibr]

DILIPandey,

Thank you for your help with this. The code works great as it was intended inside the workbook, however, it does not stop the potential recreation of the data from a separate workbook. I was still able to reference the hidden sheet from another workbook.

I suppose I am stuck with keeping the cell protection and the worksheet hidden. That will do for now. I do find it strange that there isn't a setting built into Excel to prevent external linking.

Thanks again!

 

[liveinhope]

Hi Danelskibr
You said in your initial post that the worksheet is protected and hidden. But the user can just open that external file i.e. ProtectedWorkbook.xlsm and unhide the sheet
If you want to stop them doing that you need to protect the workbook too .... you may have already done that but mentioned in you post

 

[danelskibr]

liveinhope,

Thank you for mentioning that. I have all worksheets, the workbook, and VBA Project password protected.

 

[DILIPandey]

Hi Danelskibr,

What if you put that code in Personal workbook (personal.xlsx) ?


Regards,
DILIPandey

 

[DILIPandey]

I have all worksheets, the workbook, and <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Project password protected.

Then change the name of hidden worksheet and protect the workbook structure, then no user can't unhide the sheet.

Also, you can use below vba code to hide the sheet which is a user can't unhide normally:-

Sheets("abc").Visible = xlVeryHidden

Regards,
DILIPandey