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

Excel Security Considerations

I'm working to finalize a Excel Workbook that I'd like to distribute. However, I'm not certain how to design the security for it. Specifically I'd like to know how to;

1.) Secure the entire workbook from any changes while still allowing all of the worksheets to trade information and calculations.
2.) How to initiate a "global password"
3.) If I lock a spreadsheet, does that mean it cannot update calculations that occur based on links to other worksheets within the same workbook?
4.) What considerations should be used when securing an individual worksheet?
5.) When securing the VB Code and Macros, is protecting the "project" enough?

I know security is not absolute in Excel; however, I'd like to make it rather difficult for someone to hack into the macros or change the calculations that are established. EE expertise on how to approach Excel security would be appreciated.

B.
0
Bright01
Asked:
Bright01
  • 6
  • 5
  • 3
  • +1
3 Solutions
 
Rory ArchibaldCommented:
Depends on your definition of "rather difficult" to hack into. The only way to secure the code is a project password, but that is not at all secure if someone really wants to get at it. You can additionally ofuscate the code by making all the variable names into gibberish and so on, but that will make your maintenance a nightmare, IMO. Worksheet/workbook passwords (other than the one to actually open the workbook) are trivial to crack. Your formulas should still work, but if you have code that manipulates the sheets, you will need to alter that to unprotect and reprotect the sheets/workbook when it runs (you can use the Userinterfaceonly:=True argument when protecting but that does not work for all things)
0
 
Bright01Author Commented:
rorya,

Can you elaborate on what is and how to use a "Global Password"?

TY,

B.
0
 
Rory ArchibaldCommented:
'Global password' has no intrinsic meaning to me, other than using the same password for everything. In what context are you using it?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bright01Author Commented:
rorya,

Public comboNo As Long
Public OldGlobalPassword As String, NewGlobalPassword As String

Sub lockme()
    OldGlobalPassword = "PASSWORD"
    NewGlobalPassword = "LOCKME"
    If Now > Sheets("DONOTDELETE").Range("B1").Value Then
        On Error Resume Next
            For i = 1 To ThisWorkbook.Sheets.Count
                If Sheets(i).Name <> "DONOTDELETE" Then
                    Sheets(i).Unprotect OldGlobalPassword
                    Sheets(i).Protect NewGlobalPassword
                    MsgBox "The worksheet is this workbook has been locked. Please contact B on XXX-XXX-XXX-XXX for a new workbook"
                End If
            Next i
        On Error GoTo 0
    End If
End Sub

0
 
FernandoFernandesCommented:
One suggestion to make it harder (not impossible) for anyone to see the VBA, you could
protect the code with password (layer one of VBA protection)
share the workbook (layer two), will make project become unviewable.
anyone who knows this, will be able to undo it. The good thing is, if they're using password cracking softwares, even if these softwares remove the layer one, the project still remains unviewable bcuz of layer two.

Of course a quick google search may help the 'cracker'...

For all the rest (protecting sheets and workbook), try to use complex passwords and pray, they're so easy to break...
For securing the file, use complex password and  wdifferent types of encryption, bcuz most softwares dont break all encryption types offered !

:)
0
 
Bright01Author Commented:
I'm not worried about the audience for this Workbook; they barely have Excel 2010.  However, I'd like to protect the worksheets.  If I go into properties and hide a worksheet (or extremely hide a worksheet) and secure it, it will still operate within the math and required calcuations but be hard to detect from a simple "unhide" that may be used on a "tab".  Is that correct?  Also, I still don't know what a "Global Password" is (see code above)....any idea(s)?
0
 
FernandoFernandesCommented:
yes it's correct. All values will still be referenceble, and its formulas will be calculated regardless of visible status !

now, global password is as Sid said, this concept doesnt exist in Excel ... Unless you protect everything with the same password, then you consider as 'global password' or even 'universal password' or 'super password'...
that code is determining a variable called OldGlobal and another called NewGlobal, but it could also be called HiHelloWorld... what the code is doing is, it's making sure that all the sheets are protected with the same password, thats it.
0
 
FernandoFernandesCommented:
And i dont get this:
'I'm not worried about the audience for this Workbook;'

i think you should be ! but it's just me :)
0
 
FernandoFernandesCommented:
Ops, sorry, I meant Rorya !
0
 
Bright01Author Commented:
Fernando,

What I meant to say was that the users of my workbook are not very sophisticated; nor do they probably want to deal with the complexity of the code.  However, once replicated; this workbook could be cracked and I understand that security with Excel is limited.  With that said, I believe based on the comments that you and Rorya have made, that there are ways of making it more difficult to hack. Here are the specfic questions I need answers to;

1.) If I am using a password to access a locked spreadsheet and a different password to protect the VB "Project"; what do I put into the code that Sid built for me in the "OLDGLOBALPASSWORD" section?  I do not have a universal password but rather multiple passwords for different levels.  May I use "*" or "" or just leave it blank?
2.) If I put in a NEWGLOBALPASSWORD based on a timer (again, that Sid built for me) will that replace all passwords?  What I'm attempting to do is lock down the Workbook at a given time and the code I showed above is what has me perplexed on how to adapt.
0
 
yuppyduCommented:
There are a number of software in he internet to protect excel files. Some are quite good. Most of them allow you to convert your file into an exe file and that makes it almost impossible to crack for the regular user.
0
 
Bright01Author Commented:
yuppydu,

Really?  Two questions; are they free (can you direct me to the one you like the most) and does that mean that a "user" may not need Excel 2010 to run a Excel 2010 Workbook / App.?

B.
0
 
Rory ArchibaldCommented:
I've never seen a free one. ;)
0
 
yuppyduCommented:
They are not free. I'll not comment on the pricing, it depends how valuable is your workbook. I sell an Excel application for financial asset management and I protect it with one of those softwares. Very flexible and very secure even for an advance user.
Don't want to disclose it here, if you're interested you can pm me.
0
 
Bright01Author Commented:
What's PM?
0
 
yuppyduCommented:
PM stands for private message. You can write me at: giovanni@5sada.it
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now