Excel – Automating Data Entry and Protecting your Data

NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.

Now, by default all cells on a worksheet are set to “Locked” status.  This means that if you were to apply worksheet protection, you wouldn’t be able to interact with any of the cells on the sheet.  So once you have your worksheet designed and set up the way that you want it you can select all of the cells with which you want users to interact.  (Note that you can select multiple, non-contiguous cells by using CTRL+Left-Click.  The caveat here is that if you accidentally select a cell that you don’t want to be in that series you’ll need to start all over again, as you can’t de-select a cell that’s been selected in this manner).

Once you have your cells selected, on the Ribbon you can goto Home–>Format–>Deactivate the “Lock Cell” button by clicking on it (/hol).  You can also select the Format Cells button (CTRL+1), which will bring up this dialog:

Format Cells Dialog

The Number tab is the default, so you’ll want to click on the Protection tab, where you’ll get this:

Format Cells - Protection Tab

As with the Ribbon option, “Locked” is the default, so you’ll want to uncheck it.  Now here you have an additional option called “Hidden”.  This is an additonal feature that, when a sheet is protected, will not show anything in the formula bar when a cell is selected, as opposed the formula itself.  This feature should be used on Locked, not Unlocked cells, that you don’t want users to access.  I’ve found it helpful in the past with distributed workbooks that might have complex formulas in them, as by hiding formulas you keep users who don’t understand them from getting distracted and wondering what the heck the formula is or does.  Conversely, if you want to share your brilliance with people, then by all means don’t hide your formulas (the downside is that if you’re not very good with formulas you let everyone know it…)

Protection Options/Applying Protection

Expanded  protection functionality has been available since Excel 2000, and it gives you a  lot of options that before could only be accomplished via VBA (Visual Basic for Applications) programming behind the scenes.

When you protect a worksheet you can goto Review–>Protect Sheet (/rps) and you’ll see the following dialog:

Cell Protection Options

By default the Protect Worskheet option is checked, and since you’re there to protect the sheet anyway, the option is pretty much irrelevant (in fact, if you uncheck it, you lose the ability to protect the sheet, so it’s more than irrelevant).  Next you have the option to add a password.  I generally add a password for distributed workbooks, but if I’m testing a worksheet or if I’ve protected one of my own worksheets, I don’t add one (it can be a pain to have to enter the password to unprotect, then enter it twice to reprotect it ad nauseum). 

• Now here’s a note on Excel’s security: it’s notoriously weak.  But then again, so are locks on doors, and they both serve somewhat the same function in that they’ll keep 99.9% of people honest.  If someone really wants to get past your password, or door/window lock, it can be done in about 2 seconds (but I won’t tell you how, neither will Microsoft).

You can now enter a password, which you’ll be prompted to enter again to confirm.  Press OK and your sheet is now protected.  Before you go any farther, we also need to talk about the protection options you have (maybe the password input box should be at the bottom of the dialog box, as it’s the last step?)

By default the Select locked/unlocked cells options are enabled.  In some cases you’ll want to prevent users from selecting locked cells, although I can’t really think of a good reason for not selecting unlocked cells.  Microsoft probably put that there just to give you the option, even though you’ll probably never us it.

Following are the additonal features that you can enable/disable by choice and what they mean:

• Format Cells/Columns/Rows: if you have users who like playing with their colors and fonts, then you can let them.  Although I generally don’t make this available to them in order to preserve the integrity of distributed workbooks.  I’ve seen too many users “do their own thing” with workbooks, and then you end up with a horrid mish-mash of workbooks that all essentially (maybe) do the same thing, but look different.  It’s not a good idea if you want to have a coordinated presentation, especially with customers.
• Insert Columns/Rows: another iffy option, as users can manipulate workbook integrity, but you may have some cases where users need to ability to add rows or columns in order to add data.
• Insert Hyperlinks: this one is good if you want users to be able to add e-mail addresses or links to websites, e.g. customer sites, internal sites, etc.
• Delete Rows/Columns: another iffy one here.  In a distributed workbook, you need to be pretty careful about what you let users delete, lest they delete functionality.  I’ve had too many users call me to say that “it stopped working” only to find out that they deleted a row(s) of dependent formulas.
• Sort: very handy, and probably one of the biggest reasons why Microsoft added the increased Protection options.  Prior to this the only way to sort on a protected sheet was via VBA code.
• Use AutoFilter: ditto.
• Use Pivot Table Reports:  unless you have some high-end users, this feature will probably be unnecessary.  And if you do have users savvy enough to be using Pivot Tables, you probably won’t want them to limit them with protection, unless it’s purely for data entry purposes to prevent accidents (which are all too common - I have had more than my share of experience accidentally deleting some essential cell(s) that drives a data series and had to redo my work).
• Edit Objects: probably not very necessary, unless you have a pretty object oriented worksheet/book (lots of buttons, logos, etc).
• Edit Scenarios: this goes back to using Pivot Tables.  Unless you’ve got some high-end users you probably won’t need to worry about enabling this one.  You’ll know if you do. 

The Next Step: Workbook Protection

Unfortunately, worksheet protection won’t protect you from users deleting your well set up and protected worksheet, but workbook protection will.  No matter how much work you put into an individual or multiple worksheet(s), you’re going to run into an instance where someone deletes the entire worksheet.  In some cases that’s not too bad as you can just send them a new workbook, but sometimes, especially in collaborative workbooks, it can mean a lot of work to recreate what was lost, so you’re better off protecting yourself in the first place. 

Workbook protection is easy to enable by going to Review–>Protect Workbook (/tpw), where you’ll see the following options: 

Protecting a Workbook

 You’ll want to check the Protect Structure and Windows option, after which you’ll get the following dialog box:

Workbook Protection Options

Protecting for Structure: this is the default option, and it will primarily keep users from unhiding hidden sheets (like those that may contain Data Validation lists), actually hiding, moving or renaming worksheets, inserting new ones, or copying sheets to a new workbook (a note to this is that there’s nothing to keep users from e-mailing or copying the entire workbook itself).  There are some other options that will be disabled, but they are more for high-end users.  If you’re going to take this step, then I would also add a password, otherwise your workbook is essentially just a closed door with the door unlocked. 

Protecting for Windows: is not something I’ve ever found a need to do, but it will keep users from closing, or changing the posisiton or size of a window when it opens.   Again, use a password (and remember it!)

 

Restrict Permissions: I doubt that this is someting that you’ll need to worry about unless you’re dealing with very sensitive data, in which case, Excel isn’t the distribution product for you becasue of security reasons, as noted previoulsly.  Excel’s Permission Management  deals with Microsoft’s Information Rights Management Service, which is essentially a certification program that authenticates workbooks on your end if you’re a receiver, and on the other end if you’re a distributor.  Right now it’s on a trial basis, and I havent tried it, so I can’t really speak to it.  I know that Microsoft puts a lot of effort into security, and if it’s free, then by all means give it a shot and see how it works. 

File Protection Options 

There’s another step you can take with regard to protecting workbooks, although I rarely use it.  You can protect a workbook when it opens so that a user is asked for a password to open it, and also add a requirement if it’s Read Only (no changes can be made), or Read/Write (changes can be made).  To do this goto the Office button–>Save As–>Tools (/fa).  You’ll get the following dialog:

 

Save As General Options

Now choose General Options from the list and you’ll see this:

 

 

General Save As Options

 

The first option is to create a backup of the workbook, which will create a backup copy of the workbook in the same folder as the original workbook whenever it’s closed.   It will save with an .xlb file extension, so you can tell the difference, but in my experience this is an unnecessary feature given all of the great AutoRecovery work Microsoft has done and bundled with Office. 

 

Next you have the option to set a password to either open or modify the workbook.   This password is much more secure than worksheet protection, but it too can be broken (again, not an issue for 99.9% of users, but something you should keep in mind if you have sneaky users). 

 

Finally, you have the option to set the workbook to Read-Only, which means that users can view the workbook, but not make any changes to it.  If you’re going to be distributing reports for purely informational purposes, this may seem like a good idea, but you need to be aware that all of your formulas and hidden sheets will be included in the workbook, not only making them vulnerable to copying, but it also means that your file size is will not be compacted when you distribute it.  This can cause problems for slow e-mail systems, like Lotus Notes, where large files can cause problems with regards to on-time delivery.  If you are going to be distributing a workbook for such purposes, I’d highly recommend saving it in a PDF format, which Microsoft has included in the Excel 2007 Save As options.  It will create a condensed image file of your workbook that’s much easier to distribute.  If you need to share the workbook with other users, so they can interact with it and send you changes, then just go ahead and save/send as usual.

 

That’s about it for worksheet/workbook protection (other than what you can do with VBA, but I’m not there yet).  If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.

 

Smitty

 

 

9 Responses to “Excel – Automating Data Entry and Protecting your Data”

1. Topics about Stephen-smith » SmittyPro » Automating Data Entry and Protecting your Data

   April 26 2009

   Visit My Website

   [...] admin added an interesting post on SmittyPro » Automating Data Entry and Protecting your DataHere’s a small excerptSave As General Options. Now choose General Options from the list and you’ll see this: General Save As Options. The first option is to create a backup of the workbook, which will create a backup copy of the workbook in the same folder … [...]

   Reply – Quote

   Reply
2. How I Was Able to Lose T h i r t y P o u n d s in Under a Month

   May 6 2009

   Visit My Website

   Hi, interesting post. I have been wondering about this issue,so thanks for sharing. I’ll certainly be subscribing to your posts. Keep up great writing

   Reply – Quote

   Reply
3. Anneliese

   May 19 2009

   Visit My Website

   Hi Smitty…LOVE this article. We’ve all run into this issue before. You’ve explained it well!

   Reply – Quote

   Reply
4. tony harris

   June 4 2009

   Visit My Website

   how do I copy a protected sheet and keep all the protection….so as to re-create a new protected worksheet..as many times as I need a new one?tony.harris@alcan.com

   Reply – Quote

   Reply
   • admin

     June 26 2009

     Visit My Website

     Your best bet is to save the workbook as a template (.xlt). Then you can goto File–>New and select the template, which will create a duplicate of the original without altering the original.

     Reply – Quote

     Reply
5. Jina

   June 22 2009

   Visit My Website

   I love this topic. I’ve been looking on how to locked the cells to protect the formulas i created. very helpful..thanks for sharing..jina

   Reply – Quote

   Reply
6. Rajat Ghosh

   June 25 2009

   Visit My Website

   how can i transfer the value of a cell in a MS excell sheet in to another sheet autometically?

   Reply – Quote

   Reply
   • admin

     June 26 2009

     Visit My Website

     If you want the value to be transferred as a result of manual entry you can use a Worksheet Change event; if it’s the result of a formula calculation, you can use a Worksheet Calculate event.

     You can put up a post at http://www.mrexcel.com/board2 and you’ll get a complete answer in a few minutes. Make sure to point out how you want the transfer to be triggered (manual or formula), the origination range and the destination range.

     Here’s some boilerplate change event code:

     Option Explicit

     Private Sub Worksheet_Change(ByVal Target As Range)
     ' Code goes in the Worksheet specific module
     Dim rng As Range
     ' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
     Set rng = Target.Parent.Range("xxx")
     ' Only look at single cell changes
     If Target.Count > 1 Then Exit Sub
     ' Only look at that range
     If Intersect(Target, rng) Is Nothing Then Exit Sub
     ' Action if Condition(s) are met (do your thing here...)

     End Sub

     Reply – http://www.mrexcel.com\/board2 and you\’ll get a complete answer in a few minutes. Make sure to point out how you want the transfer to be triggered (manual or formula), the origination range and the destination range.\n\nHere\’s some boilerplate change event code:\n\n<code>Option Explicit\n\nPrivate Sub Worksheet_Change(ByVal Target As Range)\n \’ Code goes in the Worksheet specific module\n Dim rng As Range\n \’ Set Target Range, i.e. Range(\"A1, B2, C3\"), or Range(\"A1:B3\")\n Set rng = Target.Parent.Range(\"xxx\")\n \’ Only look at single cell changes\n If Target.Count > 1 Then Exit Sub\n \’ Only look at that range\n If Intersect(Target, rng) Is Nothing Then Exit Sub\n \’ Action if Condition(s) are met (do your thing here…)\n \nEnd Sub<\/code>’); return false;”>Quote

     Reply
7. Hitesh bansal

   August 18 2009

   Visit My Website

   This is a very good article related to data entry and the information is very useful relted to protect your data.
   cross-linkz

   Reply – Quote

   Reply