Excel Geeking: “That Command Cannot Be Used On Multiple Selections” Error On A Single Cell

UPDATE (18-FEB-2015):

IMG_2940Remember this? Yeah, unfortunately I do too.

I wish I could say that my update from September was the end of the conversation. Sadly, it wasn’t. I started getting this error again and there was no random PowerPivot data connection in my workbook.

When I saw it pop up again, I was absolutely flumoxed. I had no idea what could be causing it, so I opened up a ticket with Microsoft again.

It took Microsoft three months and a lot of digging (I had to run all kinds of diagnostics on my machine, something that the security folks at my company were most unhappy about). I was beginning to think they would never uncover the issue until, Lo! they contacted me back and said they had found the cause.

And it’s a beauty.

It turns out that if you, using VBA, activate a sheet that’s hidden, save the file, then close it, when you reopen the file and try to copy and paste out of the file, the error occurs.

Whacky, right?

Don’t believe me? Try it yourself.

Create a macro-enabled workbook, save it as some name (doesn’t matter what), create a new module, and then paste this snippet into it:

Sub TestError()
Sheet1.Visible = xlSheetHidden
Sheet1.Activate
End Sub

In the ThisWorkbook module, paste in this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
TestError
ThisWorkbook.Save
End Sub

Now close the file.

Now open the file. You might actually note that the sheet that was hidden is unhidden again. I think this is because it is the ActiveSheet, based on how the code set it and saved it when we closed.

Copy some cells in any sheet in the workbook. Try to paste them into a new workbook.

BAM! Error message.

What’s more interesting is that if you set the sheet in question to xlSheetVeryHidden, the error does not appear. It only works (or doesn’t, as the case may be) with xlSheetHidden.

So what it boils down to is that I am a victim of my own sloppy programming.

And with that, I will (hopefully) finally close the book on this error.

UPDATE (29-SEP-2014):

I promised that if I received an answer from Microsoft as to what bug could cause this issue, I would post it. And while I can’t say that Microsoft was able to tell me why the bug occurred, they were able to tell me what caused it. So here’s an update on where this stands.

powerpivotconnectionApparently, I had an errant data connection in the workbook. A data connection to, of all things, PowerPivot.

I have PowerPivot on my machine, but I’ve never really used it. I’ve played a little bit here and there, but I haven’t dug into it to understand the nitty gritty details. Well, at some point, I must have been playing with PowerPivot in this workbook, because a data connection was created.

Unfortunately, the data connection became “bad” at some point, meaning it didn’t point to anything. If you click the “Click Here” line in the Data Connection dialog box, it burps at you.powerpivotconnection_oops

Through the simple act of removing this data connection, I was able to clear the error and begin to copy/paste normally out of this workbook.

As I said, Microsoft was able to explain what caused the error (they pointed me to the data connection), but they were unable to explain why a bad connection would throw such an unusual error. I didn’t push it. The fact that they found the cause and that I could clear it on my end was a huge help. I also don’t know if it’s just a bad PowerPivot data connection that would cause this, or if any bad data connection will cause this. I hope not to ever find out.

ORIGINAL POST (20-AUG-2014):

“If I went back to work I would want a job like yours. I love Excel.”

This is a direct quote from a friend of mine on Facebook. It was in response to my post on controlling template releases. And sometimes I agree. The ability to play, arms deep, in Excel everyday is sometimes fun.

And sometimes it sucks it hard.

It’s been a less than banner week this week. I had a major update to an Excel-based application that I tried to roll out, only to have it fail spectacularly. I struggled with this thing for approximately two days before finally saying “F*ck It”, and reverting back to an older more stable version.

Here’s what happened:

I redesigned a planning template so that there would be a little more real estate on the UI worksheet. But I still needed a consolidated table of all the data. So, as part of the submission process that saves each template to a network location, I created a simple routine to copy/paste all of the relevant data to a worksheet I called “Export”.

As part of our consolidation process, I iterate through all the XLSM files in the network location, open them one by one, and take the data from the “Export” tab, paste it into a single temporary workbook, then copy/paste that into the Big Mutha.

(I know this sounds Draconian–cause it is. MS Access is not supported architecture, so I can’t feed it to a database.)

This is the same process this application has used for four years. The only difference is that this new change copies data from the “Export” tab as opposed to the UI worksheet. Easy-peasy, right?

Wrong.

After releasing the new template into the wild, this roll up process began to throw an error. But not a reasonable error. An error completely out of context for the operation I was performing

Specifically, I got this error:

IMG_2940.PNG

You would think based on this error that I goofed in the coding of my copy/paste routines, trying to grab non-contiguous cells. That’s what this error looks like to me. But no, I was not. I got this error if I copied a simple range of cells, one column x number of rows. I got this error if I copied. One. Single. Cell.

I was pissed.

I exhausted Google over the last two days. I mined every frickin Excel forum I could find. And while I did see a couple of forum posts from people who were having the same issue, there were no replies in the thread. Cause what exactly are you supposed to do when you copy one cell and Excel thinks you’ve copied bunches of cells all over the place?

In my travels on Excel forums, I found one solution that worked half the time. If you close the file and then, instead of Opening the file you choose instead to Open and Repair, Excel will open it, attempt to repair it, tell you that it did some work, and say [Repaired] in the file name at the top of the application. The next step is to Save As the file. Same name, different name, up to you. But I found that this fix did not work unless you saved it, closed it, and reopened it. And even then, it only worked half the time. I tried coding this action, which you can do using xlRepair as the value for the CorruptLoad property in the Workbook.Open method, but it did not work uniformly. And since there were over two hundred files to apply this to, the manual effort involved was simply too much to manage.

At this point, unable to fix the issue, we rolled back the update to the previous version, cause we knew it worked. Because this error is completely out of context for the action, I’m assuming there’s a bug in Excel. There is a lot of code in these templates, a lot of which I wrote. Some type of action my code is taking must be causing a file corruption and thus this error. Therefore, come Monday morning, I’ll be giving Microsoft a call and reporting an issue.

Stay tuned. When/if I get an answer, I’ll post it.

Valentine’s Weekend Special: How It Ends

20140629-114004-42004110.jpgHappy Valentine’s Day!

It’s the holiday when we go out of our way to celebrate love.

What better way to celebrate love that reading about a robot that falls in love with a human and the tragic results that follow?

Okay, maybe it’s a stretch. But for this holiday weekend, you’ll find Part One of How It Ends free on Amazon, and starting tomorrow and Monday, you can get the full novel of How It Ends for only $0.99.

For $0.99 you can read about how the end of the world came about. What a bargain, right?

MS Access Geeking: Giving Your Form’s Buttons A Nicer Place To Live

This one is for all the MS Access geeks out there who, like myself, are not intimately familiar with all of the super-secret methods the Builders Of Microsoft Templates use to get that high-class finished look. I searched online for a while trying to figure this one out until I found a solution buried in a forum somewhere. I wish I had bookmarked it, because now I can’t find it again. But I feel like this little trick deserves to be in a spot not buried by the internet. (Which of course presumes that this blog is not one of those places. That might just be wishful thinking.)

proj2Ever notice, after downloading and opening up one of the templates available in Access, that the forms have this really snazzy beveled bump at the bottom of the form header? This is where the buttons sit, and it gives the form that little bit extra that say “monkeys didn’t throw this together”. Ever try to figure out where the bloody blue blazes that bump comes from? I did. I spent way too long trying to figure it out. Maybe I’m just slow on the uptake, but I couldn’t figure it out. Until I had a eureka moment.

Here’s the secret: it’s an image.

propsheetYup. If you go into the Property Sheet of the form in Design mode, you’ll find that it is actually an image that is set up in just the right way to make it look great. The Picture Size Mode is set to Stretch Horizontal which takes the picture and pulls it to either side of the form like Stretch Armstrong. The Picture Alignment is set to Top Left or Top Right (the left or right doesn’t matter since it’s being stretched–it’s the top that’s the important part)

Which gives you a really nice way of making the form look good, right?

Except…wait…how do I get a copy of the image?

It was like know the secret of how to make fire, without have any tinder to start it. All I needed was the picture, and I would be able to give my form that look the says “A professional did this, not my nine-year-old.” Except I couldn’t. I saw that the file in the Property Sheet was called “office.png”. I scoured the web looking for a similar image and came up with bupkis.

This is where the forum post came in. (I’ll keep looking around for it and if I find it, I’ll update this post with a link back.) The post suggested using a capture tool of some sort (like the Windows Snipping Tool, or SnagIt) to grab a small part of the screen when the form is running. Save that snip, then use it as the embedded image in the form.

Oh boy oh boy oh boy oh boy! (I’m easily excited at times.)

CaptureI did exactly that, grabbing this image to my left. I then set the properties of my new form the same way I found them in the Microsoft template. I worked like a charm.

Capture3There are some caveats that go with this:

  • If you’re going to capture the image, you’re obviously stuck with whatever color scheme you’re grabbing.
  • Because it’s an image, you’re stuck with whatever height you’re grabbing. A nifty way around that would be to extend the upper part of the image with some type of cloning tool that you can find in Adobe Photoshop. But that’s getting toward a lot of work for a minor visual display.

That’s it. Have fun with your forms.

Book Review: “Going Clear”, by Lawrence Wright

GoingClearCoverHaving taken most of December and January off from blogging, it’s time to get back to it. What better way to start than with a book review. And a humdinger of a book it is.

You may have heard of Lawrence Wright’s investigation into the Church of Scientology. His book “Going Clear” is the result of a long-form journalism article that appeared in the New Yorker that told the story of Paul Haggis’s very public exit from the Church. Paul Haggis, for those who don’t know, is a screenwriter and director, most well-known for writing and directing the movie “Crash”. Additionally, you may have heard of the documentary “Going Clear”, directed by Alex Gibney. It is due to air on HBO on March 16th, after making a big splash at the Sundance Film Festival this past year. The documentary is inspired by Wright’s book.

The book itself continues to use Paul Haggis as it’s central core around which the rest of the narrative revolves. Haggis’s early experience with the Church open the book, and after a lengthy but necessary detour exploring the life and times of L Ron Hubbard, who founded the Church, and David Miscavige, who took over from Hubbard once Hubbard was no longer well enough to run the Church, the narrative returns to Haggis.

One of the great difficulties of writing on this topic, prior to Haggis’s departure and afterward, is the lack of information about the inner workings of the Church of Scientology. The Church, which goes out of its way to maintain its secrecy, has ttired to tightly control information about its inner workings. There is, therefore, very little documentation from which can be drawn an investigation. Wright uses, as his sources, many ex-Scientologists, which have given harrowing accounts of what life inside the Church is like; official public documents, such as the Naval records of Hubbard from his time in the service during WWII; leaked scriptural content, which ex-Scientologists have managed to smuggle out of the Church as they made their escape; and the few books and investigative articles that have come before. Interestingly, for this last category, there are very few. The reason is because the Church makes a deliberate effort to undermine these kind of investigations, and, failing that, harass the authors with private investigators, lawsuits, and even framing them for felony crimes.

profile-LRHBecause limits that the Church will go to in order to protect itself seem to be boundless, this book becomes a page-turner of a story, enumerating the actions the Church has taken over the years against individuals, businesses, and even an enormous government bureaucracy (the IRS). The founder of the Church of Scientology, the prolific science-fiction writer L Ron Hubbard, is presented in a manner that show him to be at best a pathological liar and at worst a paranoid schizophrenic. The current leader, David Miscavige, is portrayed as a tyrant rivaling some recently toppled despots, who is willing to use humiliation, degradation, and even physical violence and abuse to get what he wants.

In recent years, a number of stories about the inner workings of the Church have come to light. Stories of a place called the Hole, a set of un-air-conditioned trailers sitting in the dessert with bars on the windows and security guards at the door. Stories about how church members have been made to lick toilets clean or subsist off of leftover table scraps or sleep on floors covered with ants. What’s amazing is that these Church members are typically high-ranking members of the Church’s leadership whose only sin was to land on the wrong side of Miscavige’s ire. Additional stories have emerged from former Scientologists themselves, on sites such as exscientologykids.com.

When taken as the sum of its parts, the book never truly decides what it wants to be, which may its only significant flaw. It is a compelling read, and it’s easy to see why it was a finalist for the Nation Book Award. But there is a lot of stuff going on inside its 450+ pages, all of which relevant, all of which, when woven together tell a helluva yarn, none of which take a specific stand. Perhaps good journalism is like that, letting the reader determine the stand they must take. As such, “Going Clear” is part expose on human rights abuses, part biography of the charismatic and troubled founder, part investigation as to why Hollywood is so fascinated (some would argue “taken in” or “hoodwinked”) by the Church. Many people these days outside of the Church are most familiar with Scientology based on interviews celebrities such as Tom Cruise has given, where his defense of the Church has been oddly aggressive. Wright’s book shows that interior of Scientology is much darker, and it’s perceived weirdness much deeper than what most readers know. If anything, Wright’s book is as concise a history of the Church of Scientology as one is likely to find outside the church’s officially blessed and released histories.

It is in the epilogue where Wright’s investigation (the Church might in fact describe the investigation as “muckraking”) transcends the the rest of the book. He never offers an indictment of the Church, though, if even half of the stories that ex-members tell are true, then one is certainly warranted. He also never truly defends them. The epilogue is where he comes the closest, holding the Church of Scientology and all of its troubling history and downright bizarre space-opera cosmology up against other profoundly popular and recent theologies. The most obvious is the Church of Jesus Christ of Latter-day Saints, otherwise known as the Mormons, who believe in a third book of the holy scriptures known as the Book of Mormon, a scripture based on an ancient set of holy tablets found right here in the good ol US of A. He compares Scientology, rightly or wrongly, to both cults and ancient religions such as Buddhism and Christianity. The Reverend Jim Jones, another charismatic leader, led hundreds to their death at their own hands in Guyana. Christianity, in its early days, was persecuted by the Romans who must have thought that the idea of a single god was absolutely bonkers. In both cases, as with active Scientologists, their belief is absolute. What any and all faiths rely on is that very word itself: “faith”. There are always going to be aspects of faith that are un-proveable. An atheist demands proof, which he will never get, and the believer believes blindly, never questioning whether that which he believes is maybe just a little bit crazy. In comparing Scientology to other faiths, creeds, and cults, Wright wraps up his book on a high note, reminding us without chastising us that matters of human rights abuses must certainly be addressed. Matters of individuals faith is really no one else’s business.