Searching a Workbook by Default, Take Two

by Allen Wyatt (last updated March 24, 2014)

A few issues ago a tip appeared about how to display the Find and Replace box and set the Within drop-down list to Sheet. At the time I reported that I had not come across a way to actually accomplish this, as VBA didn't provide a way to display the same Find and Replace dialog box that appears when you press Ctrl+F.

This past week I found out the way to do this, thanks to the contribution of a generous ExcelTips subscriber. The following macro shows how to accomplish the task:

Sub DoBox()
    ActiveSheet.Cells.Find What:="", LookAt:=xlWhole
    Application.CommandBars("Worksheet Menu Bar").FindControl( _
      ID:=1849, recursive:=True).Execute
End Sub

The Find method allows you to set the different parameters in the Find and Replace dialog, and then the CommandBars object is accessed to actually display the dialog box.

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!