VBA: Using the Find Method on an inactive sheet

I am developing a userform that uses the find method to search a column within a worksheet and return a result. The code is working fine.

The problem is that I intend to hide the sheet containing all of the information for the lookup. That said, it will not be the inactive sheet. When I run the find query, I get this error:

Please Login or Register  to view this content.

This is the snippet of my code that is returning an error:

Please Login or Register  to view this content.

I have modified my code to perform like this:

Please Login or Register  to view this content.

That change seems to work, but I do not know if it is the most efficient way of operating.

Any suggestions?

Hello BigBas,

If you have to use ActiveCell then you will always have to Activate the Sheet. If you can start at the beginning of column B, you could use this code...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith:

Thanks for the response. I now see how using the activecell would affect the search (I hadn't thought of that before).

The reason I used the ActiveCell as a condition is that the code is actually a "Find Next" code. I run the code to search. When it finds a selection, I have it select that range, and display it in a textbox. If the user is unsatisfied, they click the find next to find the next option. Do I have any options besides using the screenupdating method?

Entire code:

Please Login or Register  to view this content.

And, a quick follow-up question as well. If I DO have to use the technique where I select the sheet with the lookup values, is there anyway to return to the sheet the user was working on when the search is completed? There are many sheets in the workbook, and that would be a great help.

Hello BigBas,

It isn't necessary to select the cell when using Fin, FindNext, or FindPrevious. You need to store the result in a variable declared as as a Range type. Here is an example from the VBA help files on using FindNext...

FindNext Method Example

This example finds all cells in the range A1:A500 that contain the value 2 and makes those cells gray.

Please Login or Register  to view this content.

To return the Worksheet name the cell is on use the following code...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith:

Again, thanks for the response. I will try to incorporate the info you have provided into my worksheet tonight then update the post.