Dedicated cell for search

**Nigel** · 09-24-2007, 08:00 AM

I would like to have a cell or button on my worksheet which when "clicked" opens the search facility, is this possible?

Thanks and regards

Nigel

**broro183** · 09-24-2007, 09:09 AM

Hi Nigel,

Yes, it's possible. I'm off to bed now but the below should help you...

The critical line of code is:

Application.Dialogs(xlDialogFormulaFind).Show

and the implementation is discussed here:

HTML Code:

Goodluck :-)
hth
Rob

**VBA Noob** · 09-24-2007, 10:11 AM

You may find this link interesting

http://www.excelforum.com/showthread.php?t=594168

VBA Noob

**Nigel** · 09-24-2007, 12:17 PM

Many many thanks for the replies....plenty of options there for me.

Thanks again for taking the time.

Regards

Nigel

**Nigel** · 09-25-2007, 12:29 PM

It works fine except the search box opened does not have all the functions as the "normal" find options in excel 2003. The option to check the whole workbook is missing which would be useful to me, is there some adjustment I can make to the code which will then bring up the fully functional search box?

Thanks and regards

**broro183** · 09-26-2007, 08:32 AM

Hi Nigel,

I would like to have a cell or button on my worksheet which when "clicked" opens the search facility, is this possible?
Thanks and regards
Nigel

I hadn't even noticed that my initial suggestion of "Application.Dialogs(xlDialogFormulaFind).Show" only has limited functionality. However, your latest question made me wonder if we are trying to reinvent the wheel, so I re-read your first question - and I think we are...

1) If you are already using the keyboard, I think the quickest way to show the Find dialogbox will always be to press:
[ctrl + f]

2) Another way (shown below*) puts a native Excel icon on a standard toolbar, but this may not be ideal b/c it is then specific to the user's setup when it is saved in their "toolbar file".
*right click on a standard menu (eg "File") - Customise - Commands - select "Edit" in the Categories (LH options) - scroll through the Commands (RH column) until you see "Find..." - click & hold on this icon & drag it up onto one of your normal menus & then release the mouse.

3) Option 2 could be automated for other users by including the below code in a "Workbook Open" macro (created with the macro recorder):
(I'm not sure how to remove it on file close b/c ...".controls.delete" doesn't work for me)

Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
        msoControlButton, ID:=1849, Before:=11

4) Least preferred alternative due to the reported possibility of inconsistent results is the use of "sendkeys". NB: I can only make this work by having the below code activated from with in Excel and not within VBE (eg through a Worksheet_SelectionChange macro):