I am receiving a "Run-Time Error '32809', Application-defined or object-defined error", on code that was previously error free. The line that is throwing the error is the following:
If Application.ActiveSheet.Name <> "Prices" Then 'more below, all worked prior to today
Playing with the immediate window, it appears that the Sheet object no longer works, and any reference to a worksheet (through either ActiveSheet or Sheets(1) or Sheets("Prices") etc no longer works. I've checked references to see if something has changed, and I can't see anything missing.
To check that this was the problem, I inserted a line above the If Then statement of Application.Workbooks("Actual Workbook Name").Activate. This line worked, but the code still errored out at the same place. When I changed that inserted line to Application.Workbooks("Actual Workbook Name").Sheets("Prices").Activate the code errors out (same error) on this line.
I'm running Excel 2007 in an XP environment.
No, that's not it (yes, I have a sheet named 'Prices' and there is no space in the code for the name of the sheet). I could be looking for Sheets(1) or ActiveSheet and I get the error. The error is for an object not existing, and ActiveSheet should be existing. And the code worked for the last two months, but stopped working today. No updates applied that I know of (networked PC, updates controlled by our SysAdmin).
That was the obvious thing. Ok, next, F8 through the code and see what the variable is, in the Immediate Window, just before the code breaks and right after the code breaks. Do you see anything?
If nothing has changed in the past 2 months, and if everything 'seems' correct to you, maybe a simple reboot will fix the issue. I've noticed that Ctrl-Breaks build up over time. For instance, if I'm running code and do several Ctrl+Breaks, after a while, sometimes, my code will fail for no reason whatsoever. The code could fail on something as simple as selecting a Sheet. There is no reason for it, other than the build-up of control-breaks. A simple restart always work for me!