Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

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