Adding a find button to a spreadsheet

Question

My boss has asked me to add a find button on to a spreadsheet so when people who are notsavy, can click a cell and the Find option pops up.

Rather than typing "Ctrl F".

Answer

In VBA you can pop up theFind dialog with the code:

  Application.Dialogs(xlDialogFormulaFind).Show

so you just need to put a button on your sheet and add this code to the macro or Click event procedure it runs. If you use an ActiveX button (i.e., a button from the Controls toolbox) the button will generate a click event in the worksheet's event code module, so you want to add the above line of code to it so that it looks like this:

Sub Button1_Click()

  Application.Dialogs(xlDialogFormulaFind).Show

End Sub

But if you use a Forms button (from the Forms toolbar), you should add a macro to a standard macro module and assign the button the run it. The macro should look like this:

Sub PopFindDialog()

  Application.Dialogs(xlDialogFormulaFind).Show

End Sub