RT Error '9': Subscript out of range when using Workbooks.activate and Sheets.activate

[faemike]

Hello,

I'm running into some behavior that I can't explain. I've created an xlsm file which I use to sort a separate workbook (workbook A) with, at first, only a single sheet (sheet A).

I've written the code so that it takes a text box input from a form in the xlsm file (a person's last name) and on a button_click event, it captures the value and stores it as a variable. It then calls Sub M with the captured value as a parameter. This sub then activates workbook A and then immediately activates sheet A. The first time that this sub is called, I have no issues. However, on every subsequent call of this sub, I get an RT Error '9'. Interestingly, if I click "debug" and then "continue"(F5), the script completes as expected so it's not a syntax-related issue though I wonder if I'm using these calls properly...

Workbooks("Workbook.xls").Activate
Sheets("Sheet").Activate

I was originally activating the workbook in the button_click sub of the macro file and then reactivating it in Sub M but I've ruled that out as an issue by simply commenting out this workbook.activate.


Any ideas or recommendations on what to look at?

 

[igold]

With all due respect, as you are a self admitted rookie, a rookie mistake would be not using Option Explicit on every code you write.