Script Out of Range Error

[elevate_yourself]

Hello,

I have a script that until this morning worked fine but now I am getting a "Script out of Range" Error. I have bolded the line that is causing problems. Anyone have any idea's why this is suddenly an issue?

Please Login or Register  to view this content.

[Norie]

Is there a worksheet named 'LC Schedule' in the workbook that's just been opened?

Also, where is this code located?

[elevate_yourself]

Yes there is a worksheet with that name in the file that is being opened. Also this code is located in another workbook.

[Norie]

Where in the other workbook?

[elevate_yourself]

Sorry not sure what your asking. Its on a dashboard tab - not the "LC Input" tab which is used in the code above

[Norie]

As far as I can see you would only get this error if the workbook being opened didn't have a worksheet with the exact name 'LC Schedule' or the code was located in the ThisWorkbook module of the workbook you are running the code from.


PS You might want to lose all the Activate/Select stuff, for example.

Please Login or Register  to view this content.

[elevate_yourself]

hmmm yeah I know that this all works because I have been using this code for weeks now and for some reason this just came up today. Thanks for taking a look! Anyone else have an suggestions?

[Norie]

Nothing has changed?

It's definitely the right workbook that the user is selecting in the dialog?

[elevate_yourself]

Yeah nothing has changed - I added script elsewhere and was trying to re-run the model and suddenly the error occured

[Norie]

Can you upload the file that's causing the problem when it's opened?

You can scrub all the data from it.

PS I don't mean scrub the data from the actual file, just open it, scrub the data, save under a new name and upload that.

[elevate_yourself]

Unfortunately no - to remove all sensitive data would take far too long. All I can say about it is there is definitely a worksheet "LC Schedule" which has the information I need on it.

[romperstomper]

Do you have any code trapping the activate event of that worksheet?

[elevate_yourself]

ummm sorry romperstomper - im fairly new to vba and not sure what you mean by that

[elevate_yourself]

OK I seem to have fixed the issue, though I don't know why this worked. The model i'm using originally had 2 buttons: 1) to clear and 2) run model. This code was my attempt to consolidate that down to a single run button. When I originally wrote the code, I added the code to the model then deleted the 1st button, moving its procedures into the 2nd. This is what seems to have broken the code (again I have no idea why). I copied the same code into an earlier version of the model with both buttons in tact and it ran perfectly. I then tested removing the button and re-ran and was getting the above error. So I tried reversing the order of operations: I first removed the button from the model and then imported the code and now its working perfectly. So if someone can explain why removing a button (and I would like to emphasize that only the button was removed, no code was changed) would break code like this that would be very helpful. But for now im just happy it works. Thanks for the help!