Need to create a pricing model, whereby the back sheets contain the chemicals, prices, formulas and calculations.
The front sheet enables user to modify chemical proces and have new compound cost calculated on front sheet.
Workbook, sheet passwords are trivial to break.
How can we create a secure model so the formula sheets can not be accessed?
Secure Excel Model
-
-
-
As you stated, it is relatively easy to crack worksheet & vba code passwords. You cannot 100% hide worksheet formulas. One extra layer (still breakable) is to make your sheets 'very hidden' thru the Visual Basic Editor.
-
This scenario of securing an or password protection is a VERY common issue that raised. From a guy who knows no matter what you do I and many other so minded individuals can and quite happily unpick your defenses.
Excel offers nothing that can defend against anything bar the normal guys in the office, its not exactly very hard to do. Even Add ins are a snip to unpick.
The hardest are the worksheet opening passwords, but this is a time issue and will son be open by a few special tactics.
Sorry – just to underline, do not be fooled into false security, do not rely on it.
Kindest possible regards
Jack in the UK
-
I was just thinking I have written at length over this very subject, ill try to dig it out for you
Hope this might help a little more.
Jack in the UK
-
Hi,
Quote
How can we create a secure model so the formula sheets can not be accessed?FWIW:
If You have some knowledge how to create COM-add in then You can create a much better protected solutions then using XL:s built-in facility.
The point is that You can have the formula sheet in a COM-add in and call differents functions, lookup-tables etc.
However, COM-add in require an insight and a knowhow before we can apply this technique.
-
-
This is the feed that I spoke of;
http://www.ozgrid.com/forum/viewthread.php?tid=536
Here is another feed I have posted large amounts on this very subject which will assist you greatly, please read al the posts on this feed, to build up a picture of what’s going on and why guys say what they say, as this is a rich feed.
http://www.ozgrid.com/forum/viewthread.php?tid=410
Hope this offers a bit more for you.
Jack in the UK
Marker Security
-
Just to add...
The following is based upon my own circumstances and may or may not be suitable for you.
Whenever I have some workbook or a portion therein which contains confidential information, otherwise important information, or even many formulas, I always embed them in code. In large workbooks, I store a complete backup in obscurely located non-Excel files with no file extension. This may seem extreme but it has worked very well for me and given me peace of mind. Most of the workbooks I have created for work are reused in cycles such as a billing period. Each period is backed up in custom created files which are much smaller than a workbook. The restore process in instantaneous. This allows you to keep the workbook in a static state when the actual data is stored elsewhere. If this is within your scope, you may want to consider this method.Tom
-
Thank you for your research Jack, and to all others who replied. I am coming to the conclusion that we should be looking at a compiled application, as the formulas that calculate the customer's sell price must not show under any circumstances.
Once again, thank you for your replies and time.:thumbcoo: -
Hi Anonymous
Not to harp on too much about what has already been said, but I totally agree that Excel's security is quite weak :no:
As has already been mentioned though, you can make the sheet XlVeryHidden and then Lock the VBE. Far from foolproof though, as has been pointed out also.
Here are some other ways, that when combinded, make it much harder to view the formulas.
Store the formulas in a out-of site area on the sheet, e.g IV5000 etc.
Make the font white and add Conditional formatting to do the same.
Make the font very small and one like Marlett etc.
Give the cells a Custom Format of ;;; This hides them.
Set the Sheets ScrollArea Property to A1:A2 in the Sheets Property Window.
Place this code in the Private Module of the sheet.
CodePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Me.ScrollArea = "A1:A2" Me.Visible = xlSheetVeryHidden Me.Protect Password:="Secret" End Sub
Place this a LONG way down in the Module so that the Module appears totaly blank unless they scroll.
While in the VBE go to Tools>Option-Editor Format set the font colors to white, very small and to a non-readable style.
Last, but not least, lock the VBE.
This is enough to keep out the majority of people, but as you can imagine, not totally secure.
Also, try our ascociates at Password-Find as I believe they may have an encryption tool for the VBE.
-
Dave
Re: Place this a LONG way down in the Module so that the Module appears totally blank unless they scroll.
This comments needs to be in the tips forum, it’s that good and totally like Jack uses diversions, all that can be done is print or delete the module, only seasoned gurus will know that
A hot tip Dave
Jack in the UK
:thumbcoo:
Edit: Typos!!!
Poor quality, I’m sorry -
-
Guys,
I tried making my VBA font white a while ago..... I wouldn't swear to it but doesn't it set it to white forever ?
ie next morning, my font was white in a totally different workbook - I couldn't see what the hell I was doing, so had to change it back
:o
-
Chris
Your idea is good, but I crack many wkbks at work, and this is the very fist thing I do soon as I’m in.
Unhide columns and rows, sheets whatever.
CRTL+A
Font Auto
EnterA must do, and very limited defense, guys I work with do this as routine, now and cross reference = to separate sheets or wkbks, all very standard stuff.
Sorry top be negative, just do not want this seen as much defense. Its not.
Very sorry buddy.
Your buddy in South London
Jack in the UK
-
Hi Jack
I wont even tell the embarrising situation in which I cam across the tip about placing the code a long way down in the module :no:
Chris. Right you are: :redface: Ignore the tip about changing the code text.
-
Hi Dave,
Welcome to the club :lol:
I guess that we all, sooner or later, face a similar situation like the one You indicate...
I have some stories about it as well but I won't tell the circumstances :coolwink:
Nevertheless, as I say we learn very much by our mistake :cool:
-
Try this site by Dermot Balson. He's a great guy and some of his ideas may help you out.
http://www.webace.com.au/~balson/InsaneExcel/Encryption.htm
AJW
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!