Opening the "find all" feature on workbook launch

  • Thread starter Thread starter mark_the_yeti
  • Start date Start date
M

mark_the_yeti

For my next trick, I'd like to have the "find all" feature pop up as soon as
the workbook opens.
The workbook is to serve as a searchable database of material safety data
sheets, where all chemicals in the plant are listed and linked to the
appropriate MSDS.
For example, when a user opens the workbook, the "find all" box should
appear, they should be able to type in "oil" and get about 30 results because
the search was too broad... but that's the general idea.

I wrote code to find the first instance of a word (below), but it doesn't
completely solve my problem. Thanks so much for the help.

Private Sub Workbook_Open()

Item = InputBox("Tell me what you're looking for")

Cells.Find(What:=Item, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

End Sub
 
How about just showing the Ctrl-f dialog?

Application.Dialogs(xlDialogFormulaFind).Show

or
Item = InputBox("Tell me what you're looking for")
if item = "" then
'do nothing
else
Application.Dialogs(xlDialogFormulaFind).Show arg1:=Item
end if
 
Those options both work OK, but I want the user to be able to choose from a
list of responses, not just scroll through one by one.
 
Tell them to click the "Find All" button (if the version of excel they're using
supports it).
 
Back
Top