Error when running Macro with other excel workbooks open

[VBA_Learner88]

I have a Userform that runs a macro to perform a series of operations by reading data from a database I created in the same workbook but on a different sheet. If I have other excel files/workbooks open when I run the macro via the Userform, I get a 'Run Time Error 9: Subscript is out of range'.

What is causing this problem and how can I fix it?

Thanks for your help.

 

[JoeMo]

You increase your chances of getting an answer if you post your code.

 

[VBA_Learner88]

It is a very big code to post here. Basically, the macro does not work (Cannot use any of the control buttons on the userform) when other excel files are open, instead I get the run time error 9. That is the problem. Here are my theories:

1. The program I wrote is confused as to which workbook to pull data from, thus the out of range error.

Does this make sense?

 

[Trebor76]

The program I wrote is confused as to which workbook to pull data from, thus the out of range error

Yes, if the tab you're referring to isn't in the active workbook it would cause a 'Run Time Error 9: Subscript is out of range' error message.

Using the ThisWorkbook function before opening any other file should solve the issue, like so:

Option Explicit
Sub Macro1()

    Dim wrkMyWorkBook As Workbook
    
    Set wrkMyWorkBook = ThisWorkbook
    
    wrkMyWorkBook.Sheets("Sheet1").Select
    
    Set wrkMyWorkBook = Nothing
    
End Sub

HTH

Robert

 

[JoeMo]

It is a very big code to post here. Basically, the macro does not work (Cannot use any of the control buttons on the userform) when other excel files are open, instead I get the run time error 9. That is the problem. Here are my theories:

1. The program I wrote is confused as to which workbook to pull data from, thus the out of range error.

Does this make sense?

Maybe, but that's like asking someone if you have used the right colors for your painting w/o actually showing them the painting. Consider at least posting the portion of the code that generates the error message.