“Should I go down and do that for her, or should I be a good, conscientious neighbor and make sure your washer & dryer work properly?”

We agreed that making sure everything was working correctly would be the right thing to do.  I mean, there’s no need to go back down the hill only to be called back if there’s a problem right?

Well, the washer/dryer worked like a champ and my wife learned how to run the lawnmower, so there was an added bonus.

I love yardwork.

When she came back the scruffy old guy was gone, as was her original shoe!

She went up to the clerk and asked if anyone had just left in a hurry.  Well, as a matter of fact, a couple of people had just left, but nothing out of the ordinary.  Cyndi explained about the guy and how her shoe was now missing, pointing to the mismatched pair on her feet.  She explained that “I could have had a blonde moment, and misplaced it, but I realy don’t think so…”  The clerk was equally grossed out and gave Cyndi the shoes she had been trying on free of charge.

So she left with a new pair of shoes and only one of the original pair she’d been wearing.  On the way home guess what she sees on the side of the road?  Her shoe!  Naturally it’s been thoroughly cleaned upon its return.

At about the same time I was on my way home from getting my truck smogged and saw some scruffy old dude hitchhiking (that’s a primary means of transportation up here).  Cyndi told me about her experience when she got home and we realized that the hitchhiker I saw was probably the same guy who stole her shoe…

Watching a woman in a shoestore is weird enough, but who steals a woman’s smelly shoe?

On the way home, just as we came through the 45 mph zone in the little town of Davenport (Cyndi and Campbell were following behind me) a little suv pulled out onto Hwy1.  After only a few hundred yards the thing sverves onto the shoulder over a curb (like someone reached for a dropped cell phone).  Clearly overcorrecting, the driver then lost control, got hung on the curb, flipped several times and landed on a slope in the bar ditch on the driver’s side.  My decision wasn’t if I should stop, but how to park my bike heading downhill without losing it?  I settled on just riding the front tire into the curb on the shoulder at an angle and hoped it wouldn’t roll either down the highway or off into the ditch…

I got off and ran up to the car just as the driver, a 20-something girl, was trying stand up inside the totaled suv.  I climbed up the roof of the car (a big bashed in part where it hit the curb gave me a good foothold).  She looked up and frantically said “I can’t figure out how to get out…”  Seeing she didn’t appear to be too badly hurt, I looked around and told her to get back…Shielding her face with my arm, I bashed out what was left of the broken passenger window, swung my right leg inside the car, braced it against the passenger seat and helped pull her out, got her to the curb and sat her down to see how she was, ask her name, etc.  By this time other people were running up to help so I went to my bike and took off my helmet and gloves, then went back and put my jacket on her.  Cyndi had the 911 operator on the line as I was walking back to my bike doing my best charades impression of “Call 911!”.  She had the 911 operator cracking up as she’d already been on the phone with them and said “Oh great, my husband’s trying to tell me to call 911, duh…”

Then about all we could do was comfort the driver and give her some water trying to keep her from going into shock before CHP/ambulance arrived.   Campbell was especially sweet though - she saw me put my jacket on the girl and told Cyndi “I have a good idea: she needs my sleeping bag more than I do.”  Then she pushed it out the window to Cyndi.  That is one sweet little girl.  Such little things like that make me feel so blessed.  And she had a ball sitting on the window of our truck watching the firefighters, ambulance crew, CHP and everyone else.  Giving her sleeping bag to the driver is going to be her sharing day topic for class this week.

Fortunately the driver was relatively unscathed other than a few cuts and some bruises she’ll certainly be feeling in a day or so (along with one very wrecked car).  She’s also lucky she was only going about 40, much faster and she’d have been dead.

When the firefighters & paramedics showed up I went back to my bike to get out of their way and waited, knowing we’d have to stick around to talk to them and CHP as we were first responders (funny how so many people want to get in the way and have to be told to move).  During that time I had a chance to start looking around and talked a bit to the head firefighter on scene.

Noticing something that had just become painfully obvious I asked:

“Do you guys have anything on your rig for poison oak?”

“No, why?”

“We’ve all been standing and working in a thicket of it…”

Without skipping a beat he said “Yeah, that shit’s all over the place down here…”

The rear of the suv was in a thicket of poison oak bigger than I’ve ever seen, literally hundreds of yards long and well over 10′ tall in places.  We were walking on the stuff, she and the paramedics were working on top of it/sitting it it (our sleeping bag was in it…)

We got home and scrubbed off - so far so good, but I’m not looking forward to figuring out how to get it off of my kevlar leathers…Maybe it”ll rub off on the damn cat, who has a very irritating habit of sleeping on them.

Oh yeah, I didn’t mention that there were more fun events that day: I lacked the foresight to charge my battery the night before, which I rembered just as we tried to leave and the bike wouldn’t start.  So I had to quick charge it while Cyndi and Campbell left, because I was holding them up.  I got to Davenport and stopped to grab a drink in the little convenience store there, and of course when I came out the bike wouldn’t start - great!  Didn’t charge it enough - Doh!  Cyndi rounded up some jumper cables at the beach and came to bail me out, then we continued to the beach.

As I’m taking my gear off and putting it in the back of Cyndi’s truck my boss (Campbell’s godfather) calls and says he’s having car problems and is off on the side of the road.  Uh ohhh, do you need us to come get you?  Turns out it wasn’t car troubles, but car sickness troubles - his girlfriend was experiencing her first migrane and the “scenic” mountain route, including very tight, twisty roads and rapidly alternating light/shade from the trees was making her violently ill - repeatedly.  For about 15 miles he couldn’t get more than a mile at a time without having to stop for a vomit break…When they showed up she passed out in the car, but recoved sufficiently to come down to the beach and put something back into her stomach.  Now she knows why the road we live on “Bear Creek Road” is called “Barf Creek Road” by the timid and unprepared…

I had the good fortune to have a few pile on today:

I talked to my boss today and ***’s really worry about going down to *** next weekend to have a what I am assuming is a biopsy.

*** comment to me was that “autopsies are really painful for me…”

I sent *** a message in return:

• “Tell the Dr. to be gentle during your autopsy – they can be fairly painful while you’re alive.”

Then there was the one about the missing solo hiker in Washington who was unfortunately found dead:

• “Her body was discovered by hikers wedged between rocks…”

If that’s the case, then how/where did they find her? More importantly, how did they get out?

And a good one from today on a data entry/integrity issue:

• “it’s all these boneheads that input data from around the country.”

What’s data in different parts of the country have to do with it?

And one from my daughter’s principal:

• I do want to point out that [our school district] will be entering its sixteenth year of declining enrollment, and earlier this month, we gave preliminary layoff notices to 2.8 teachers and one administrator.

FTE equivalents not withstanding , how do you lay off .8 of a teacher?

I’m gonna’ be digging more on this one, so feel free to pitch in!  I think it also fits in several business sections about how NOT to write press releases/corporate documentation.

I’ll add more and see if I can get them in chronologial order if I get the chance, but for now it’s more for her grandparents and godfather to see…I’d love to see other comments that kids can come up with, so please feel free to contribute!

• 1/28/09 - In front of her grandparents, when we were discussing animals:

“You know, squirrels are really nice,  unless you try to grab their nuts, or take them…”

• 12/21/09 - A rhetorical question I suppose:

“Dad, feeding a pig bacon is really, really wrong.  I mean, what is bacon made out of anyway?”

• 12/04/09 - Overheard while Campbell & Cyndi were playing a game:

“Mom, you’re really freakin’ me out…”

She’s 5…

When I have time I’ll recount Campbell’s argument for gum.  That was a fun one from a true soon to be sales professional!

08/01/09 - Or somewhere therabouts…

I have been plagued by a grand chicken experiment.  Cyndi decided that it was vital to our existence to support a chicken population.  This ongoing saga has seen us through about 40+ chickens in stages ranging from tiny chicks in a box with an incubator light to chickens in the hen house, with us (Cyndi and Campbell anxiously awaiting eggs)…So were the neighbors’ dogs…

My bane was the inevitable rooster that would appear.  Fortunately for me the dogs took care of most of them, except for one: Lucky (who wasn’t).  If you’ve ever had a rooster strutting around at 3 in the morning you’ll understand my position on the whole chicken thing.

I finally got the go-ahead to dispatch Lucky while the girls were out and did so.  Unfortunately, they drove up just as Lucky and his head separated and he went for his great last dash…Campbell thought the entire thing delightful and even wanted to help Mom prepare him for dinner. 

So now to it: the best comment I’ve ever heard regarding a chicken was from my daughter:

“Dad, what’s the best place for a rooster?

In the pot, COOKING!”

Gotta love that child!

5/6/09 - Campbell came up to me and was concerned about her first sleepover tomorrow, because her friend doesn’t clean up her room after playing in it; could I teach her to do it?  I told her that was up to her friend’s parents to teach her, but I could ask them both (the girls, not the parents) to clean up the room after playing in it.  Her reply to me was:

“You don’t have to make cleaning up a chore, Dad.

You can make it a learning experience. 

Like sorting by colors or size or shapes. 

Like put all of the brown bears in the toy chest.”

Mind you she’s 4, and when I reminded her of this an hour later and tried to employ her own logic she had a meltdown…

• We just happen to live in an area that’s made up of coastal mountains (coming from Colorado I call them hills) covered with beautiful, giant redwood and pine forests, in which the elusive hippies live.  I typically keep my hair short, because I predominantly ride a motorcycle and don’t use hair gel of any kind (Iiagine trying to clean that crap out of a helmet over time).  Mind you it’s not jarhead short, but when it gets long enough for helmet hair I get it cut.  But, I don’t need to know when to get it cut because Campbell let’s me know:

“Daddy, you’re a stinky hippie!”

• Playing in the Rain

We have a series of depressions alongside our driveway that turn in to a system of ponds/dams/streams when it rains and (despite being about 99% little girl) Capmbell loves playing in the rain.

One rainy afternoon, Cyndi and I were under our carport working on something as Campbell was stomping about in the mud boots her grandmother got her for Christmas, and Cyndi said: “Campbell, I think you missed one.”  To which she replied: “

Nope, got ‘em all Mom!”

Random comments that need no explanation:

• • “Mom, why don’t snakes have legs?”
  • “I like to wear socks to bed sometimes, they keep you from chewing on your toes..”
  • “Don’t be afraid, there’s nothing to worry about, you just need to be calm and it will be over in a minute…”  (As she came up with scissors and a roll of tape, trying to make a bridge between the couch and chair for a caterpillar she found in the yard).  She was much more reassuring than the nurse’s assistant who take my blood!)
  • “Dad, can you help me find my lizard?” (She caught three swifts (lizards) while playing in the yard and wanted to keep them.  Too bad she brought them in the house…)

Excel (and most other Office products) come bundled with what’s called Visual Basic for Applications (VBA).  It’s a subset of the powerful Visual Studio.NET programming language that Microsoft uses to write Office applications and they allow us to access/harness some of that power through VBA.  Most other product providers don’t let you do that, which is one of the reasons that Excel is so much more powerful than other applications.  For instance, Google Spreadsheets have no internal programming capability, so you can’t automate any tasks, while Lotus 1-2-3 only gives you the ability to record keystrokes.  In that sense you have more automation ability with Lotus than Google, but it is strictly limited to what you can do within the Lotus interface itself; you can’t get behind the scenes like you can with Excel, and it can make a huge difference to your daily work.

Case in Point: some time ago, when I first joined a business unit in San Diego, our assistant spent 8+ hours every Monday calling our satellite offices for their weekly sales numbers (by sales rep), which they then read to her over the phone and she entered into a weekly leaderboard report in Excel.  We had a computer order entry system, so realizing that the data had to exist in an electronic environment, I was able to set up a simple query from the system and then automatically populate a similar report in Excel using VBA.  What used to take 8 hours all of a sudden took about 8 minutes (including the time it took me to get a cup of coffee).  I was further able to automate tasks to the point where I could essentially take a day or two off each week because of how much time I saved at repetitive tasks (somehow I never did though).

I didn’t start out as a programmer, I’m not one now, and I don’t expect you to end up as one (unless you find it to be greatly beneficial to you, in which case you just might), but I will introduce you to how to start automatiing to your everyday work by recording macros.

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”.

The Macro Recorder (is your friend)

Excel (and most other Office Programs) have a tool called the Macro Recorder, and you’ll also want to know that it’s probably your biggest friend as you start the foray into trying to automate your daily life.  If you’re not familiar with it, here’s a homework assignment you can do right now and then come back (note that VBA procedures are generally undoable without preparation, so make sure you try any code, sample or recorded, on a TEST workbook): I want you to think of a fairly repetitve task you undertake and goto the Developer tab, then Record Macro (/lr).

When you first start recording a macro you’ll get the following dialog box:

Record Macro Dialog

I’ll get to the options you have here in a minute, so go ahead and hit OK for now. 

Now go ahead and start doing what it is that you normally do for this particular task.  When you’re done, hit the Stop Recording button (/lr).  Now you can go to another worksheet and if you’re still on the Developer tab hit the Macros button (/lrpm), which will bring up a list of all the public macros stored in your workbook (public means that you can see and call the macros from the Excel side of the world).  Select the one you just recorded (it will probably be called “Macro1″), then hit the Run button and see what happens.

Macro Dialog List

VBA will now repeat what you recorded on the other sheet.  Pretty cool huh?

I have to note that the Macro Recorder is just that, a recorder, so it literally records everything that you do.  If you make a mistake and undo it, that will be recorded and repeated when you run the macro again.  It will also record all of your screen navigation, like moving from sheet to sheet or even scrolling up, down, across, etc., so while it’s a great tool, it can also be pretty inefficient as you (and your end users will see that activity - there are ways to get around it, but that’s another topic).  On the upside, it will also record the correct file path/name of a workbook, query, external database that you might be trying to access, which you might (would) probably otherwise screw up on your own.  I use the recorder all the time (as do many MVP’s) for just this reason.  Remember though, that you’re just telling a computer to do what you tell it to do.  One of my favorite quotes from fellow MVP, Ken Puls, is: “I hate it when my computer does what I tell it to do vs. what I want it to do!”  Recorded macros lack lack the intuition that you can add yourself within VBA. 

So now you know a bit about the macro recorder, and frankly some people never move past it, as it’s enough for them and that’s fine.  In fact, I know many people who are Finance professionals who never get past this point and it’s not a problem (I also know quite a few “Excel Professionals” who have never even made it to this step!)

Now I’ll move onto the options that you have when you begin recording your Macro(s).

Record Macro Options

Going back to the Record Macro dialog there are four options you have:

Record Macro Dialog

1) Macro Name - You have the option to name the macro whatever you want, although you need to follow some naming conventions (no spaces or special characters).  You’ll know if you entered an invalid name as Excel will let you know and you’ll have to try again.  I generally try to give my macros a fairly intuitive name, like “FormatSheet”, because if you start compiling a bunch of macros how do you know which one to choose?  Believe me, I’ve been tricked by ambiguous macro names and had to rebuild a workbook because of it.  By default VBA will name your macro “Macro1″, and each macro you record after that will be named in succcession (Macro2, Macro3, etc .)

2) Shortcut Key - Next you have the option of assigning what’s called a Shortcut key, which will allow you to call a macro with a keyboard shortcut instead of initiating the macro dialog list (I’ll explain how to call your macro(s) without shortcut keys in a minute). 

Note that shortcut keys are activated with the CTRL key, so you don’t want to use reserved keys, like P, otherwise you’ll overrride your abilility to print with that shortcut (the same goes for C-Copy, V-Paste, X-Cut, and most importantly Z-Undo - You don’t want to disable that one!  And if you do overwrite native commands you have to give them back, neither Excel nor VBA will do it for you, so be careful!) 

With that in mind, you can also take advantage of the Shift key as an additonal step.  To do this when you’re ready to assign the shortcut, hold down the Shift key and that will create a Ctrl+Shift+Key sequence (this is generally the route that I choose so I avoid any mistakes).

3) Store macro in - What’s that?  You have a few options when you record a macro of where you want to put it (it’s one of those behind the scenes kind of things).  If you click the arrow button to the right you’ll see that you can put your recorded macro in “Personal Macro Workbook”, “New Workbook”, or “This Workbook”.  You’ll generally want to choose to the “This Workbook” option, which will, as it suggests, store your macro in the workbook you’re recording the macro.  The “Personal Macro Workbook” option allows you to make a macro available to any open workbook (but that’s a topic for another discussion), and the “New Workbook” option is something I’ve never really had to use, so I’d ignore it and just stick with the default (note that if you do select something other than “This Workbook” the recorder will stick to that selecection for any other macros that you record, so you’ll need to be mindful of that, and switch it back if you have changed it…I’ve had a lot of macros “disappear”, because I put them in the wrong place).

4) Description - This is where you can add comments to your macro, so that when you open the Macros button from the Developer tab it will tell you what the code does (or at least is supposed to do…) 

Here is an example of recording a macro and naming it yourself, assigning a Ctrl+Shift+Key combination, then adding comments:

Record Macro Dialog

5) TheFinal Step - Calling the Macro and putting it to use…Now, from the developer tab you can click the Macros button (/lpm), and you’ll get a dialog box that lists all of the macros that you have available:

Macro Dialog List - with Comments

Just so you have an idea  of what multiple selections look like, here’s what my options look like when I do it (I keep several macros in my Personal.xls):

Smitty's Macro List

Here you have several options:

1) Run - The most common command, which will Run your code. 

2) Step Into - This is for more advanced use and allows you to actually step into the code with the VBA environment for testing and debugging.  Go ahead and try it if you’re interested (ALT+Q will exit the VB Editor and take you back to Excel).  If you choose this option, the F5 key will execute your code immediately and F8 will let you run it one line at a time.

3) Edit - Ditto.  This will take you directly to your macro’s code.  The same holds true with F5/F8 here.

4) Create - I’m not sure why Microsoft left this option here, as I’ve never used it (or seen it enabled for that matter).

5) Delete - Does what it says, so be careful.  There’s no Recycle Bin from which to retrieve a deleted macro.

6) Options - This gives you the chance to add a shortcut key and comments if you didn’t choose to do so when you first recorded your macro.

Now you know all you need to know to start recording your own macros and begin automating some of what you do everyday.  Bear in mind that as I mentioned earlier that the recorder is just that and nothing more.  Once you get the hang of recording some macros, you’ll start to wonder if you can do other things that the recorder just can’t capture.  The good news is that you can with some practice.  The best thing you can do if you want to  continue past what the recorder is to start understanding the VBA code itself.  Anytime you want you can select Edit from the Macros List and take a look a the code.  Let’s say you record a macro that applies some formatting; when you look at the code you should be able to see what the code is doing, based on the steps you took when recording it.  It’ll look a lot like Greek at first, but you’ll get the hang of it pretty quickly.

In my next post, I’ll discuss how to step beyond the recorder and begin editing your own code, then how to even start writing your own, which is when things really start to get fun and a whole new world of automation opportunity begins.

Good luck and most of all have fun!

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

 

 

—–Original Message—–
From: Barrister John Williams [mailto:barrjohn@earthlink.net]
Sent: Saturday, April 25, 2009 2:26 AM
Subject: Barrister John Williams

From The Desk Of Barrister John Williams

John & Associate Chambers

37 Sathorn Tai Road,

Bangkok Thailand

Email: barrjohnwilliams_bangkok@yahoo.com.hk

 Dear Sir/Madam,

Please kindly accept my apology for sending unsolicited mail to you I believe you are a highly respected personality considering the fact that I sourced your profile from a human resource profile database on your country. Though, I do not know to what extent you are familiar with events.

I am Barrister John Williams, a Solicitor. I am the Personal Attorney to Mr.Steve Anderson, who used to work with Siamrak Company Limited. On the 21st of April 2003, his wife and their three children were involved in a car accident along Sukhumvit Express Road.

Unfortunately, they all lost their lives in the event of the accident. Since then I have made several enquiries to your Embassy to locate any of my client’s relatives, this has also proved unsuccessful. After these several unsuccessful attempts, I decided to trace his relatives over the Internet to locate any member of his family but to no avail, hence, I contacted you to assist in repatriating the money left behind by my client in a Finance Company, Particularly, the Finance House where the deceased deposited the US$15 Million (Fifteen Million United States Dollars only).

Consequently, this Finance House issued me a notice to provide the Next of Kin to claim the US$15 Million (Fifteen Million United States Dollars only) in their custody within the next ten official working days. Since I have been unsuccessful in locating the relatives for over 4 years now, I seek your consent to present you as the next of kin of the deceased to claim the fund as the Next of Kin to him so that the fund will be transferred to your account by the Finance House.

Upon receipt of the fund, I will come over to your country to meet with you for the disbursement of the fund and then you and I will share the money in this order: 50% will be for me, 40% will be for you.While 10% is for execution of the fund incase of any expenses you encountered during the process of the fund into your account. I have all the necessary legal documents that can back our claim we will make with the Finance House.

All I require is your honest co-operation to enable us seeing this deal through. I guarantee you that this will be executed under a legitimate arrangement that will protect you from any breach of the law.

You are needed as a next of kin to inherit your brother left fund

Best Regards .

Barrister John Williams (Esq).

—–My Response—–

Oh my god!

Really, me?

Never in all my days here in Pahrump, Nevada, did I think such a thing could happen to me!

I would love to help you out, and Pahrump is lovely in July.  We even have an airport, but I doubt you can get a direct flight from Bangcock, maybe Tulsa, but not Bangcock.

Yew can stay with us if you want, provided yew don’t mind dogs (or kidz).

I’d be happy to let yew present me as next of kin, as mine’s all ded, that’s why the dogs.  They keep me compny.

I’m honest and hard werkin, that is, since I got laid off from the gypsum mine, it’s used to make drywall, they’ve got that in Bancock don’t they?  And I could use the money cuz of the mesotheliayomama I got down there.

Yew just let me know.  I’ll even have my cousing come pick you up at the airport, or we do have a Trailways bus stop.

If yew don’t want to stay at the trailer wif us, I can talk to Johnny down at the motel and he’ll fix yew up good with a rrom.

Looking forward to your visit, dumbass.

Of course, if this generates some interest, then you should feel free to respond to: Barrister John Williams [mailto:barrjohn@earthlink.net] personally.  I think he’d like that (whoever he may be)…Take care and Caveat Emptor goes both ways.

From Mr. Excel user Hermanito:

• Real programmers don’t comment their code. If it was hard to write, it should be hard to understand and even harder to modify. (unknown source)
• My crystal ball broke and my ouija-board is on back-order, so please explain your problem as clear as possible, I don’t want to have a seance with my ancestors to know what’s not working for you!

Recently I was involved in a discussion where a poster wanted some help sorting some data, and was convinced that Excel 2007 doesn’t support sorting despite being given instruction on how to do it:

“Hi Smitty,
Excel 2007 does not have ascending or descending order.”

Ummm, yes it does:

“Yes it does. It’s just cleverly hidden. You can use the keyboard shortcut–> ALT+D+S+S or activate the Data tab, where you’ll find the sort options.”

Finally another poster put us out of our misery with this great response:

“You have two MVPs helping you, and other than turning your monitor upside down I doubt anyone can think of a quicker way.”

**********************
And a classic regarding protecting your code:

“Beware though, that a user with a middling knowledge of VBA will be able to get to your code. Personally I don’t have to worry, as my users are complete nincompoops…”

*************************

Term of the Day: SPREADSHITS

We’ve all seen some absolutely atrocious, poorly designed and less than functional spreadsheet that someone’s developed.  I often wonder why people who have no idea of how a good spreadsheet should be designed are given computers in the first place (let alone a driver’s license), but they’re out there, and can generally be found in almost any office.

Based on having to deal with a series of non-functional, poorly designed Excel workbooks distributed by none other than a division president (a Finance guy no less) and not having the ability to correct them let me to coin the appropro term “Spreadshit”.

If you can’t fix ‘em, at least you can make fun of  ‘em!