Debug Code to Search Entire Workbook

**atwy** · 11-23-2014, 09:40 PM

Hi,
I'm using the following code to allow my entire workbook to be searchable using Ctrl-F instead of just 1 sheet by default.

Private Sub Workbook_Open()

Application.CommandBars.FindControl(ID:=1849).Execute
SendKeys "%(t)%(h)W~{ESC}"

End Sub

It appears to work but there's always a pop-up msg "code execution interrupted". How can this be fixed? Thanks in advance

**karedog** · 11-23-2014, 11:17 PM

Hi,

Please enclosed your code inside [CODE]your code[/CODE] tag.

Please Login or Register  to view this content.

You must set the "wait mode" as true, so the sendkeys() runs correctly.

Regards

**atwy** · 11-27-2014, 02:37 AM

Hi Karedog,
That helps to remove the error.

However, the code appears to type a "W" in a the active cell whenever I open the workbook.
How can the code be modified so that it still achieves its purpose of allowing search within the entire workbook but does not output the "W"? Thanks!

**karedog** · 11-27-2014, 05:09 AM

It seems like there is something interrupt during sending keystrokes process (not happened in my PC both Excel 2003 and Excel 2007).
You can try to use timer to launch the code later in hope that all "heavy process" that happen when the file is opened have already finished.

Put this code in the module :

Please Login or Register  to view this content.

The code for ThisWorkbook :

Please Login or Register  to view this content.

You can adjust how long Excel will wait before lauch the code by changing the TimeValue("00:00:05") (5 seconds later) to something like TimeValue("00:00:10") (10 seconds later) if "the heavy process at the beginning" is still running.