Macro stopped working, but works when I step through it

[looknsee]

I've recently added several new macros, and they are functioning well, but some of my old macros are not working. When I execute the old macros, I get a pop-up window with "Windows Visual Basic for Application" as it's title, and " 400" as the message; there also are an "OK" and a "Help" button. The "Help" button just fires off a generic top level Microsoft Help window. This MsgBox does not come from my macro.

So, from the Developer ribbon, I choose "Macros" and choose the impacted macro. When I step through the macro, it executes as expected.

Note: Several of my "old" macros behave this same way.

Any clues? Thanks.

 

[davesexcel]

No really, we can't see the code from here.

 

[antialias401]

If the Error 400 is not telling where the error is, and not highlighting the offending line for you...
You can try to trap the error yourself.
(There are way more elaborate ways of doing this! But this should start you off)

Sub x ()

   'Place this at the head of your code
   On Error GoTo WhyAnnoyingError
 
  'Place this that end end your code
WhyAnnoyingError:
   MsgBox Err.Description
End Sub

My bet is that the "old" macros are trying to select worksheets or named ranges that no longer exist (-;

 

[looknsee]

Okay -- I don't understand this, but I've found a work around that made this problem go away.

I was using a customize ribbon containing all the macros associated with this big honkin' spreadsheet. I had added a few new macros, but I didn't touch the old ones. It surprised me that the old macros stopped working.

My workaround: I re-created the custom ribbon, removing the old macros and adding them back in. Then, no problem. Go figure.