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.

Advertisements

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

Add yours

  1. Please tell me you have found a fix for this. It only just started appearing for me when I try to add a new sheet to a workbook, making sure there were no multiple selections anywhere.

    1. The problem was in the Pivot Caches elsewhere in the workbook. Somehow they had become corrupted (despite being less than 10 minutes old) and they were preventing any new tabs from being added to the workbook.

      So, I wrote a subroutine that ‘wipes’ all the pivot caches in the workbook by setting to false the SaveData variable, then saving the file. Elsewhere in the code it closes and reloads the file, which then runs the second part of this subroutine to switch the settings from False back to True (which usually requires a refresh first) and then re-saves.

  2. Actually , I figured out that if you use Ctrl to select multiple items, and you click on a box and then unclick it, you get the error message, but if you never unclick a box, it will work fine.
    I’m using excel 2007

  3. Another issue that i found is
    If any page of your sheet having “Filter” option true. then you get this error.
    I check my whole sheet and False the “Filter” option. then its work.

  4. Thanks, this saved me god only knows how many hours.

    The cause of my issue was slightly different than yours, but the error and solution are the same. I’ll describe my problem for anyone who might end up here for the same reason I did.

    Trying to add a worksheet threw this error. Prior to the error, my routine looped through worksheet names looking for any that fit a certain criterion. One of those sheets being hidden was enough to throw the error and break everything, even though the hidden sheet was never explicitly activated, and changes to the workbook never saved. It would appear from this and the scant other information I could find, that performing any sort of action in relation to any hidden worksheet is enough to cause this extremely frustrating error.

    Simply un-hiding the sheet prior to the offending part of the routine, then hiding it again after the search was complete was enough to stop the error.

    Again, many thanks for documenting this and steering me towards a simple (if inelegant) solution.

  5. Thanks for recommending the Open & Repair option as a possible solution. It worked for me and I’m glad I didn’t have to dig deeper to this frustrating error!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: