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





mark_the_yeti wrote:

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

--

Dave Peterson

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.

"Dave Peterson" wrote:

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





mark_the_yeti wrote:

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

--

Dave Peterson

Tell them to click the "Find All" button (if the version of excel they're using
supports it).



mark_the_yeti wrote:

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.

"Dave Peterson" wrote:

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





mark_the_yeti wrote:

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

--

Dave Peterson


--

Dave Peterson