Show modal to False works, but now....How to specify a specific workbook in VBA

OK, this should be pretty basic (I hope), and I feel kind of silly for not knowing how do to this....

My wife busted my chops about my latest and greatest userform, because she cannot use any other spreadsheets when it is running. I did some research and found the showmodal to false in the properties, and that does seem to let me play in other sheets. The only problem is that if one does not click back on the specific spreadsheet for the userform, my macros crash. I am 100% certain that it is happening because of the way I am defining where to find my data:

Please Login or Register  to view this content.

The way it seems to work is that if I click on the right workbook, to make it "active", everything is perfect. How do I tell it to not look in the active workbook, but to always look in the #assperwk sheet of the BobMCCommentPrep.xlsm?

I will also be writing to other sheets in the same workbook.

Thank you in advance, and happy New Year.

BobR

Thank you TMS.

I tried BobMCCommentPrep.xlsm.worksheets... and BobMCCommentPrep.worksheets.... and both are giving me a 424 object required error. This is what I used:

Please Login or Register  to view this content.

What did I do wrong?

Sorry, I misunderstood.

I tried the new code, and it is back to giving me a run time error #9 - Subscript out of range, if I am not actively in the BobMCCommentPrep workbook. Anything else I might be missing? Here is the relevant part of the code:

Please Login or Register  to view this content.

When I debug, it is highlighting the first VLookup.

Doh!

OK, Same workbook. That is only one section of my lookups. Looking at it now, I see that I am looking through about 6 different sheets. Is it ok to just do the set ws multiple times?

OK, I got it. I just needed to add the workbook to the vlookup explicitly

Please Login or Register  to view this content.

Thank you very much

That is awesome. Thank you.

The problem with being completely self taught, is that the teacher does not ever know any better.

I figured out the vlookup thing last year and have been using the heck out of it. I will look further into the with blocks, as it appears to tighten things up considerably. I am still in brute force and ignorance mode, and hope to graduate to a little finesse next year.