Set up search box to, by default, search workbook in the extraoptions?

  • Thread starter StargateFanNotAtHome
  • Start date
S

StargateFanNotAtHome

I googled and googled for my message but, as with all group googling
we do, not all come up reliable at all. I asked some time back for a
way to set search options ahead of time so that the search box that
comes up has the workbook option selected and showing already. The
answer to that post was an add-in that does, indeed, do a lovely job.
But here with Excel 2007 and/or the setup, the add-in just doesn't
work. Also, would like to have the option built-in and not rely on an
add-in esp. since I'm handing over the reins to the person they
staffed this position with permanently.

Is there a script that can do this for us without relying on any add-
ins, pls?

Thanks so much! :eek:D
 
S

StargateFanNotAtHome

Uh-oh. You guys in this ng are absolutely great about responding that
since there's been no answer, I didn't put my request in properly,
most likely.

Is there a script that can pull up the search box with the default not
to search in the active sheet but to search in the active workbook? I
need to have this option built in and since I'm handing over the
reins, it would be difficult to install the search plugin.

Thanks! :eek:D
 
S

StargateFanNotAtHome

Well, I kept looking. I didn't find a solution to the workbook
problem, exactly. The only thing I could recognize amongst all the
find codes I found was one I stumbled is a message that said to select
all the sheets first. That does, indeed, work. But it sure is
scary! <g> As an extremely last resort, I suppose I could leave a
modified script advising the user that the sheets are selected (though
don't know how to monitor the state so that this message would pop up,
say, once the find box was closed), but quite frankly, I'm not sure I
really want to go that route.

But there must be something ... So to further jumpstart this thread,
perhaps what I've found so far might help ... (?)

-------------------------------------------
Sub FIND__Standard_EXTENDED()
'SourceURL for idea to select all sheets first, then do a find:
http://www.excelbanter.com/showthread.php?p=817169
'SourceURL - Find: http://www.mrexcel.com/forum/showthread.php?t=71530
'SourceURL - Select Sheets (since SelSh giving me 1004 at home):
http://www.computing.net/answers/programming/select-all-excel-sheets-with-vb/5902.html

'Sheets.Select ' didn't work at home
'ActiveWorkbook.Sheets.Select ' didn't work at home
'Sheets().Select ' didn't work at home

Sheets.Select ' works at office (???!!!)
Application.Dialogs(xlDialogFormulaFind).Show

End Sub
Sub FIND_Simple()
'SourceURL: http://www.mrexcel.com/forum/showthread.php?t=71530

Cells.Find(What:=InputBox("Please enter your search criteria",
"Search"), _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub
-------------------------------------------

So the "sheets.select" in the "FIND__Standard_EXTENDED" above is not
ideal at all. Was hoping to find another way to get the find to be
able to search through the entire workbook.

The clue _might_ lie in the second find coding I found, shown above
with title "FIND_Simple". In the area where SearchOrder and
SearchDirection is found, is there no option for "search entire
workbook", or something? That way I don't leave the user vulnerable
with all the sheets selected? Again, it comes back to the problem
that I don't know what to search for so I can't seem to find
anything. I'm sure once someone provides the syntax, a ton of
messages come up <lol>. That's assuming, of course, that this can
even be done without going the "sheets.select" route.

Thanks! Much appreciated! :eek:D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top