Earlier I complained about how you can’t lock down Calculated Columns in Tables, while still allowing users to insert new rows. Dick gave me half a hack in the comments of that post, and I’ve come up with the other half to give you an almost-foolproof way to protect those precious Calculated Columns from your almost fools.

Step 1: Unprotect the entire rows that your Table occupies.

Step 2: Park a shape over the Calculated Column. Leave it’s protection as the default ‘Locked’. This will act as a shield once the sheet is protected.

Step 3: Format the shape so that it has no border, and a white fill that is 99% transparent. (If it’s 100% transparent, then Excel will let users click right through it, to the cells behind).

Step 4: Add some DV that stops users from changing the formula in the column, should they navigate around the shape shield via using the arrow keys. Dick’s formula below ensures that someone will have to spend an awful long time typing before they are allowed to overwrite the existing formula.

Step 5: Add a pop-up input message that politely warns them not to mess with your calculated column, should they manage to reach it using the arrow keys. This is still needed, because while they can’t change the formula, they can still clear it using the Delete key.

Step 6: Protect your worksheet in a manner that allows row insertions/deletions.

Step 7: Go vote for Zach Barresse’s UserVoice request for Microsoft to fix Tables so that you don’t have to use 6 hacks to work around their “feature”.

This works a treat: Try as they might, they can’t select the unprotected cells in the Calculated Column by clicking on them. If they click in the bordering cells and use the arrow keys to navigate behind the shield, they get warned off politely:

…and yet they can still insert new rows:

…and as you can see, the unprotected formula in the calculated column copies down just fine.

Look how special I am.

This is it. From what I can tell, 25,000 is the last milestone that can be achieved. So I’m at the top of the mountain.

I haven’t had as much time to answer questions on SO lately, but I still find answers on there all the time. I love the way the site is organized and the outstanding UI. I wish I could get the comments box on this site to behave just like their answer boxes. And by “wish”, I mean I want it to be that way without any effort on my part.

I did happen to answer a question the other day about the “Loop without Do” compiler error. As usual, properly indenting your code avoids these errors. I guess people didn’t like the question as it got a few downvotes. I’m not so critical. I think people tend to forget their early struggles as they become more proficient with something. I remember when I first starting learning Ruby, I didn’t know enough to formulate a non-stupid question. It’s not knowing what you don’t know that can be the most frustrating. And a little throw away tip like indenting can be really helpful.

I’m really thankful there are sites like SO (and NNTP back in the day) that give me an avenue to help people. I get so much more out of it than I give that I feel guilty (not really).

So you’ve got a file with dozens of PivotTables in it. One day you hit Refresh All, only to see this complaint:

…or perhaps this variant:

What’s happened is that the size of the PivotTable has increased, and the PivotTable is now trying to occupy space where another Pivot or Excel Table already lives. So you know what has gone wrong. But you have no idea where. You’re either going to need to eyeball each and every bit of each and every worksheet (including the hidden ones), or you’re going to have to run a bit of code. Something like this:

…which does this:

Our story begins on a cold and bleak desktop. Excel, an aged application , ignores an invitation to Christmas dinner from his nephew UserVoice. Excel turns away desperate pleas from 222 voters who seek an upgrade to Tables in order to provide data integrity and usability for their poor users. Excel only grudgingly allows his overworked, underpaid Tables to be used while a sheet is protected, and even then, only to conform to the bare minimum of the existing Sheet Protection functionality.

At home that night, Excel is visited by Lotus 1-2-3’s ghost, who wanders the Earth, entwined by heavy patents and dependency chains forged during a lifetime of greed and selfishness. Lotus 1-2-3 tells Excel that it has one chance to avoid the same fate: Excel will be visited by three spirits and he must listen to them or be cursed to carry dependency chains of his own, much longer than Lotus 1-2-3’s chains.

The first of the spirits, The Ghost of Microsoft Past, takes Excel to scenes of Excel’s boyhood and youth, reminding him of a time when he was more innocent. The boyhood scenes portray Excel’s lonely childhood, his relationship with his beloved sister Word, and an office party hosted by his first employer, Mr. Gates, who treated Excel like a son. They also portray Excel’s neglected fiancée Access, who ends their relationship after she realizes that Excel will never love her as much as he loves unstructured data.

The second spirit, the Ghost of Microsoft Present, takes Excel to a joy-filled help forum of people gathering the makings of a PowerQuery, and then on to a celebration of business intelligence in a data-miner’s PowerPivot data model. They then visit Acme Inc, where we meet Jolly Jeff, an otherwise happy analyst who’s Table-driven macro-free Excel templates are seriously unprotected. The spirit informs Excel that Jolly Jeff’s carefully constructed templates will soon be corrupted by ignorance and laziness unless the course of events changes. Before disappearing, the spirit shows Excel two hideous, emaciated columns of data named Calculated_Column and Freetext. He tells Excel to beware the former above all and mocks Excel’s complete lack of concern for their unprotected state.

The third spirit, the Ghost of Microsoft Yet to Come, shows Excel a business office in the future. The ghost shows him scenes involving the death of a disliked application. The application’s funeral will only be attended by local businessmen if donuts (of the charting variety) are provided. When Excel asks the ghost to show anyone who feels any emotion over the application’s death, the ghost can only show him the pleasure of Stephen Few. The ghost then shows Excel the application’s neglected grave:

Excel: 1985 – 365.
Here lies one who frivolously cavorted with the entire office while not using adequate protection.
Recalculate AND Die.

Sobbing, Excel pledges to the ghost that he will change his Table Protection to avoid this outcome. Excel awakens the next day a changed program. Excel spends the day with Mr Gates’ family and anonymously sends a large update to Tables to the Weir home for Christmas dinner. From then on Excel began to treat everyone who uses Tables in a protected workbook with kindness, generosity and compassion, embodying the spirit of User Experience.

…is for Microsoft to fix how Sheet Protection and Tables interact. I want it more than world peace. I even want it more than cold beer. See…I even put it on my Christmas wish list:

What am I talking about? That template above is a great example: Let’s say I want to send you all some templates for you to fill out and send back. Being a prudent developer, I’ll use Excel Tables, and those tables will have a mixture of Calculated Columns with the formulas locked down (because I don’t trust you one bit), and free-text fields (because I want you to think that I care about what you have to say).

That’s what I’ve done above: I’ve got a RANK column that automatically assigns an incrementing integer to each entry, and I’ve got a freetext field where you can put your own requests, if for some reason you don’t share my world view. And I also want to let you insert additional columns, in case you want more than three things.

I better push Ctrl + F1 to bring up the Format Cell dialog, and make sure those genius formulas in that Calculated Column will be protected when the sheet is locked:

…and I better make sure that the ‘Request to Santa’ column is NOT protected:

Awesome…all I need to do in order to activate this protection is to lock the sheet, while still allowing row inserts and deletes:

And all is good, until some moron decides that Cold Beer comes in a distant third behind some additional thing they would like to add to this Christmas wish-list, like “New President”. So they right-click within the Table Row while Tra-la-laa-ing, only to be stopped mid laa: the Insert > Table Rows Above option is greyed out. :-(

But being persistent, they decide to force the issue by selecting the entire row, and then right clicking. And indeed, this time they encouragingly see that indeed the Insert New President… option is still available to them:

…only to find that Excel has trumped their desire:

But weirdly, when they click OK, Excel decides that it can in fact do it, kinda:

It begrudgingly inserted the new row, but it didn’t autofill the formula in the protected column. Meaning while our hapless agitator can fill out the freetext field with their new second-greatest wish, they can’t rank it. Meaning Santa might simply ignore it as invalid input:

God forbid they would want to do something equally trivial, such as extend the Table by either trying to type underneath it, or by selecting the bottom right cell and pushing Tab or Enter, because that accomplishes nada. As does Sorting and a myriad of other things that the Protect Sheet dialog swears it will let you do.

I just cannot fathom why this bug hasn’t been fixed. I recall Zack Barrasse talking about it years ago, along with some other stuff he’s mentioned on the Excel UserVoice site. Sure, I could write some VBA to add a custom ‘Insert Row’ entry to the right click menu, but I try to keep my templates macro-free as much as possible. Some absolutely need to be macro free. And I wouldn’t need macros anyhow if this functionality actually did what it says right there on the box.

Please go vote for this. And Microsoft, please go fix it…It’s got 191 votes already, and counting. And it is just sooo broken.

I’m looking to hire a BBj developer in the Omaha, NE area. Why would a reader of an Excel blog care? I’m glad you asked.

BBj used to be called BBx. And that was formerly known as Business Basic. If you’re a decent VBA or VB6.0 programmer, BBj will look pretty familiar to you. I’ve been coding in it for that last couple of months and, other than the constant trips to the documentation for syntax help, it’s going pretty well. The official IDE is Eclipse with a plugin, but we’ll be switching to UltraEdit very soon.

Here’s the basics

What you’ll do:

• Fix bugs reported by users
• Add new features, both large and small, as business needs dictate
• Enhance existing features for stability and usability
• Monitor system health and correct issues timely

Required Skills:

• Bachelors degree and three years of software development experience or
• Equivalent software development experience

Desired Skills:

• Experience programming in BBj, BBx, or Business Basic
• Experience programming in Visual Basic 6 or VBA
• Experience in the travel center, c-store, or wholesale fuel industries
• Experience with Subversion, UltraEdit, and iTop

Send me an email at dick@kusleika.com if you’d like more information.