Program by Levels
Tutorial on VBA for Excel (Macros)
bringing you the simplest and most efficient ways
to do things in VBA for Excel
VBA Lesson 9 of 33: VBA Security and Protection in Excel
(Levels: Beginners, Intermediate)
Sometimes you send a workbook with macros to a colleague. If he can't get them to work it is probably because his security setting is at "High" . Just tell him how to change it in "Tools/Macros/Security" .
An Excel file (.xls) cannot be infected by one of these viruses that appear regularly on the Internet but somebody can develop VBA procedures (macros) that can harm your data and your computer seriously. So set the security level of Excel to "Medium" (Tool/Macro/Security) and each time you open a workbook that contains macro the following dialog window will appear.
Adopt the same attitude as you have with documents attached to Emails. If you know the origin of the file you may enable the macros if not click on "Disable Macros" and you are fully protected. You can look at the workbook but the VBA procedures (macros) are not operational. You can go to the Visual Basic Editor to take a look at the macros and if they don't look suspicious close the workbook and re-open it enabling the macros. If in any of the modules or other elements of the workbook you see "API Function" it means that the programmer is trying to access your computer through Microsoft Windows THAT IS SUSPICIOUS.
Password Protecting the code
As an Excel-VBA Developer you might want to protect your code so that nobody else may modify it. In the VBE editor go to "Tools/VBAProject Properties/Protection". Check the box and submit a password. Make sure that you save the password somewhere you will remember because cracking VBA passwords is very expensive.
Hiding and very hiding Worksheets
In one of your workbook you might want to hide a sheet that contains confidential information (salaries and other parameters). If you just hide the sheet users can unhide it. There is a VBA way to hide a sheet without protecting the entire workbook. To view the sheet one has to go to the Visual Basic Editor and change the property of the sheet. If your code is protected, he also needs the password. So the sheet is very hidden and only you can get to it to modify its content. Select the sheet in the Project window of the Visual Basic Editor and set the visible property at xlVeryHidden. See an example in the free workbook "vba-tutorial-editor.xls"
Protecting the Sheet
To protect and unprotect a sheet programmatically you will use the following code.
If you just want to protect the sheet without a password you will use:
Activesheet.Protect
Activeshet.Unprotect
But if you want to use a password the code becomes (not forgetting that the password is case sensitive):
Activesheet.Protect "YourPassword"
Activeshet.Unprotect "YourPassword"
Read Only
When you develop a reporting template you don't want the user to be able to complete it and then save it over the template. That is when you modify its properties to "Read Only". I Windows Explorer or "My Computer" find the file and right click on it. Select "Properties" and then check "Read-Only"
Another way to do this is when you save the document click on "Options" and follow the instructions. With this approach the inconvenient is that each time the file is opened a dialog box is shown asking the user for the status of the workbook.
Protecting the Workbook
There are many levels of protection that you can set for the workbook. First you might want to forbid anybody from opening the workbook unless they know a password. To do so in Excel go to "File/Save As" and click on "Tools/General Options".
For intermediate Users
Password Protecting a single VBA Procedure
If you want a certain VBA procedure to be protected with a password you will use this code that calls for an InputBox. In the input box you cannot use a special password character. For such a password functionality you will need to develop a userform as shown in lesson 3-2. Remember that this password is case sensitive.
Sub proProtected()
Dim varAnswer As String
varAnswer = InputBox("Submit your Password.", "Password Needed")
If varAnswer <> "Sci" Then
MsgBox "Wrong password. Procedure aborted."
Exit Sub
End If
' and here would be the procedure
End Sub
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple