Subscript out of range error when opening another workbook

[rleribaux]

Hi,

For my job I made a pretty big workbook doing a whole lot of calculations and everything is working as it should but I run into a problem when I try opening other workbooks alongside it.

For some reason, when I open another workbook with or without any VBA-code in it, the private worksheet_change sub from one of the sheets in the first workbook gets called on. Here I get the subscript out of range error on the line below.

The sheet this private sub is written on gets hidden with a macro as well. The error only happens after the macro has run so when the sheet is hidden. I tried solving it by writing "if worksheet("sheet").visible = true" at the top of the sub to try and ignore the code when the sheet is hidden but the subscript out of range just moved to this line instead.

And while I'm at it, as you can see in the note (these are for colleagues) all I want to do here is recalculate 3 cells after the code above this line has run but I only got it to work by recalculating the whole file. Is there a reason why worksheets.calculate and range.calculate didn't do anything?

Thanks in advance!

 

[rleribaux]

After looking for a solution for a few days, I just found one right after I posted this. Replacing the "if worksheets().visible" by "if ThisWorkbook.worksheets().visible" solved the error. I still don't fully understand why the worksheet_change gets called on when opening a new one and why the calculate range didn't work so if anyone has any ideas feel free to reply.