One Solution To Error 400

[thunderhawk6630]

I have developed a Macro enabled workbook at work, and it has multiple sheets. I thought I had made it impossible to mess up, but a user was complaining about all the macros causing a 400 error message.

I asked for them to email me the file and sure enough the problem was on my machine too. A lot of Google searching was getting me nowhere. All the macro buttons I put on the sheet gave the same error. I opened up the editor and tried running them directly, same thing.

Here's what happened. In an effort to try to reduce errant entries by users, I have a majority of one of the worksheets protected. What the user had done was, inadvertently selected five sheets at the bottom. The macro was trying to run on multiple sheets, one of which was protected. I fired open a blank workbook to recreate the problem, thinking it was just selecting two sheets, but that wasn't it. The issue is selecting multiple workbooks, one of which had protection.

The real error was caused by trying to run a macro on a protected sheet, or on a protected cell. I was unaware that you can run macro's on multiple sheets at the same time by selecting multiple sheets (Control Click to select individual, or Shift Click to select groups), and then running the macro. Be extremely careful with this, and it is a good thing to be aware of.

But what I found to be most odd, was that if you select multiple sheets, save the file, and reopen the file, multiple sheets are still selected. So even when he emailed me the file, multiple sheets were still selected, giving me the same error. It was just chance that I noticed he had them selected, I could have gone mad chasing this down.

Hopefully this helps someone who is trying to chase down the error.

TL:DR - If you are getting a 400 error, first check to make sure that you don't have multiple sheets selected, your macro may be trying to run on multiple sheets and trying to run on a protected cell.

 

[6StringJazzer]

Can you share the code?

I was unaware that you can run macro's on multiple sheets at the same time by selecting multiple sheets (Control Click to select individual, or Shift Click to select groups), and then running the macro.

This sentence suggests an interpretation that a macro runs on a sheet. That is not quite the concept. If the code refers to an unqualified object owned by a sheet, it defaults to the ActiveSheet to find that object. It runs on whatever the code says it runs on--it all depends on how the code is written. I suspect that the situation you are describing is a flaw in the design of the code rather than any kind of issue with Excel. If the code iterates through all cells in Selection, as an example, it should take into account the possibility that Selection might span sheets and might contain locked cells on a protected sheet.

But what I found to be most odd, was that if you select multiple sheets, save the file, and reopen the file, multiple sheets are still selected.

This is completely consistent with how Excel saves the state of a file. It always saves the current selection, in terms of sheets and cells.

 

[thunderhawk6630]

Jazzer, you are probably correct that my code is the issue. I am by no means an expert. I will often qualify my code to check for the name of the sheet before running, so if it somehow gets activated on the wrong sheet it will end. But I have 1261 lines of code, and 47 different macros. I know there are more efficient ways to write the code, but it works, and I have thoroughly tested it. But someone still found a way to break it.

As for how Excel saves the state, I just thought that it was interesting. I learned something, and I wanted to let others know. Getting a 400 error stinks, because you are literally in the dark as to where to go.

 

[6StringJazzer]

it works, and I have thoroughly tested it. But someone still found a way to break it.

Testing: The process of finding the bugs that you expect to find. Someone will always find a way to break it.