Default Find Options

[rickle]

I have an excel macro that runs to set my default find/search values so
that it searches by columns and looks in the values rather than
formulas. There is one more value I'd like changed...I'd like to have
the default find to look at the whole workbook, rather than just sheet.
I'm sure it's just a single line that needs to be added to the class,
I just don't know what that line is.

Here's what I have in my personal.xls:

# (in module1)
Public gclsEventHandler As Class1

Sub auto_open()
Set gclsEventHandler = New Class1
End Sub

# (in class1)
Private WithEvents mxlApp As Excel.Application

Private Sub Class_Initialize()
Set mxlApp = Excel.Application
End Sub

Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub

Private Sub mxlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
''' Ignore this workbook and any add-ins.
If Wb.Name <> ThisWorkbook.Name And Not Wb.IsAddin Then
Dim FoundCell As Range


With ThisWorkbook.Worksheets(1)
.Range("a1").Value = ""
Set FoundCell = .Cells.Find(What:="", _
After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
.Parent.Close savechanges:=False
End With
MsgBox Wb.Name & " was just opened."
End If
End Sub

 

[Don Guillett]

try sheets.select
code
sheet1.select

 

[Dave Peterson]

It doesn't look like that option is exposed to the programmer.

You could fiddle around with Sendkeys to show the options (if it isn't shown),
then choose workbook from "within".

But it sounds like it would be very easy to have things go wrong.

 

[rickle]

try sheets.select

code
sheet1.select

How would I incorporate sheets.select into the vbs code? Admittedly,
I'm not much of a vbs person. The above code is a combination of code
I got from a few different excel newsgroup articles.

 

[Don Guillett]

try this
Sub FindInAllSheets()
For Each sht In Worksheets
With sht.Cells
Set c = .Find(what:="dd", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do


MsgBox sht.Name & "!" & firstaddress

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Next

End Sub

 

[rickle]

I tried putting that into a module, but it didn't seem to work. My
original code works by setting the default "look in" to "values" and
the default "search" to "columns" instead of "by rows".

What I'm trying to accomplish is when I bring up a new *.xls workbook,
I want to hit CTRL-F to bring up the find/search menu and have it set
to workbook, columns, and values.

Don, I appreciate your effort on this. I am truly clueless when it
comes to excel macros and vbs. I blanked out my personal.xls and put
your code into a module but it didn't seem to work. When it comes to
perl, python, and all shell scripting, I'm your man, but I'm a total
newbie to excel macros and vbs.