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

Maintaining spreadsheet security whilst performing data connection refreshes

When you protect a spreadsheet with "protect sheet" & "protect workbook" to effectively protect your IP. It seems that you need to lift all the protect to allow data connections to update.

Is there a recognised work around for this that would stand up to a penetration test?
All I can think of is a VBA script to perform the following

Unprotect workbook
Update all connections
Protect workbook

All the user has to do is press the escape key to interrupt the script during the data update and the security is no longer applied

I am sure there has to be a better way to update multiple data connections maintaining spreadsheet integrity
0
Chris Ogilvie
Asked:
Chris Ogilvie
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
The only way I know of to update data connections in a protected sheet is as you've already said - unprotect, update and re-protect. To counter the use of the Escape key, you could use the Workbook_SheetSelectionChange in the ThisWorkbook module and check if workbook/worksheet is protected and if not, protect it. While the sheet may be unprotected immediately after the Escape key is pressed, as soon as they select another cell it will be protected again.

Bear in mind though that protected Worksheets and Workbooks are fairly easy to break into.
0
 
Ejgil HedegaardCommented:
If you just want to refer to the imported data with formulas, and don't need to see it, you could have the data connections on hidden sheets.
Then update will work, also when the workbook is protected.
0
 
frankhelkCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Wayne Taylor (webtubbs) (https:#a41869399)
-- Ejgil Hedegaard (https:#a41870580)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

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