When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian

Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

This is just great! Thank you Dave.

Brian

"Dave Peterson" wrote in message
...
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for
the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way
you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to
get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

Dave,

More or less related:

Every time I open a new wokbook zeros will be visibly represented, so I
always have to uncheck that option in toolsoptions etc.

In C:\program files\microsoft office\office\xlstart I put a "map1.xls" of
which all sheets have the zeros option unchecked. When I start Excel that
map.xls pops up, as expected, but for every extra sheet in that workbook and
any new workbook I open the zeros option has to be unchecked again.

I simply want every new sheet of every new (and preferably also excisting)
workbook to have the zeros option unchecked by default.

What should I do to accomplish that?

Jack Sons
The Netherlands

"Dave Peterson" schreef in bericht
...
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for
the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way
you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to
get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

Personally, I'd use a macro to toggle that setting and run it on demand.

But you could use a workbook that looks when a window is activated and turns off
the display.

If you put this code behind ThisWorkbook and save the workbook in your XLStart
folder:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub xlApp_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
ActiveWindow.DisplayZeros = False
End Sub

Each time you activate a different sheet or a different workbook, that setting
will be turned off.

=======
But seriously, I don't think I've ever turned off zeros in real life. I like
seeing them. (Yes, it would cause changes to workbooks that I shared with
people who like them off!)

I'd just add a macro like this:

Option Explicit
Sub ToggleZeros()
With ActiveWindow
.DisplayZeros = Not .DisplayZeros
End With
End Sub

(I don't really like stuff that looks too much like magic!)



Jack Sons wrote:

Dave,

More or less related:

Every time I open a new wokbook zeros will be visibly represented, so I
always have to uncheck that option in toolsoptions etc.

In C:\program files\microsoft office\office\xlstart I put a "map1.xls" of
which all sheets have the zeros option unchecked. When I start Excel that
map.xls pops up, as expected, but for every extra sheet in that workbook and
any new workbook I open the zeros option has to be unchecked again.

I simply want every new sheet of every new (and preferably also excisting)
workbook to have the zeros option unchecked by default.

What should I do to accomplish that?

Jack Sons
The Netherlands

"Dave Peterson" schreef in bericht
...
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except for
the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way
you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to
get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

--

Dave Peterson

Dave,

I did as you said, it works fine. Now map1.xls has your code in the
thisworkbook module, should I save it "hidden" in xlstart, like
personal.xls?

Strange thng (to me) is that when I start Excel it opens with map1.xls. When
I open a new workbook it is called (by default) map1 (no extension) and
subsequent started new worbooks are called map2, map 3 etc. (Map is the
Dutch word for book or workbook) Is that te way it should be? I expected
that the workbook opened after map1.xls would be automaticaly named map2.xls
etc.

Jack.

"Dave Peterson" schreef in bericht
...
Personally, I'd use a macro to toggle that setting and run it on demand.

But you could use a workbook that looks when a window is activated and
turns off
the display.

If you put this code behind ThisWorkbook and save the workbook in your
XLStart
folder:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub xlApp_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
ActiveWindow.DisplayZeros = False
End Sub

Each time you activate a different sheet or a different workbook, that
setting
will be turned off.

=======
But seriously, I don't think I've ever turned off zeros in real life. I
like
seeing them. (Yes, it would cause changes to workbooks that I shared with
people who like them off!)

I'd just add a macro like this:

Option Explicit
Sub ToggleZeros()
With ActiveWindow
.DisplayZeros = Not .DisplayZeros
End With
End Sub

(I don't really like stuff that looks too much like magic!)



Jack Sons wrote:

Dave,

More or less related:

Every time I open a new wokbook zeros will be visibly represented, so I
always have to uncheck that option in toolsoptions etc.

In C:\program files\microsoft office\office\xlstart I put a "map1.xls" of
which all sheets have the zeros option unchecked. When I start Excel that
map.xls pops up, as expected, but for every extra sheet in that workbook
and
any new workbook I open the zeros option has to be unchecked again.

I simply want every new sheet of every new (and preferably also
excisting)
workbook to have the zeros option unchecked by default.

What should I do to accomplish that?

Jack Sons
The Netherlands

"Dave Peterson" schreef in bericht
...
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except
for
the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder.
Have a
macro in that workbook that does a find (and sets all the stuff the way
you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes
to
get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can
anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options
first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

--

Dave Peterson

You could actually just include that code in your personal.xls file itself.
(You may have to a little tweaking to merge it into your existing workbook_open
event--but very small tweaks.

I didn't realize that "map" was the equivalent of "book". If you have map1.xls
in your xlstart folder, it'll load when you start excel. I have a workbook
named book.xlt in my XLStart. It's the template that is used for any new
workbooks I start. But I don't have book1.xls in my XLStart folder.

Unless you're doing something pretty weird, the only time you'll see the .xls
extension is when the file has already been saved once. It's standard for new
workbooks to not have any extension.

Jack Sons wrote:

Dave,

I did as you said, it works fine. Now map1.xls has your code in the
thisworkbook module, should I save it "hidden" in xlstart, like
personal.xls?

Strange thng (to me) is that when I start Excel it opens with map1.xls. When
I open a new workbook it is called (by default) map1 (no extension) and
subsequent started new worbooks are called map2, map 3 etc. (Map is the
Dutch word for book or workbook) Is that te way it should be? I expected
that the workbook opened after map1.xls would be automaticaly named map2.xls
etc.

Jack.

"Dave Peterson" schreef in bericht
...
Personally, I'd use a macro to toggle that setting and run it on demand.

But you could use a workbook that looks when a window is activated and
turns off
the display.

If you put this code behind ThisWorkbook and save the workbook in your
XLStart
folder:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub xlApp_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
ActiveWindow.DisplayZeros = False
End Sub

Each time you activate a different sheet or a different workbook, that
setting
will be turned off.

=======
But seriously, I don't think I've ever turned off zeros in real life. I
like
seeing them. (Yes, it would cause changes to workbooks that I shared with
people who like them off!)

I'd just add a macro like this:

Option Explicit
Sub ToggleZeros()
With ActiveWindow
.DisplayZeros = Not .DisplayZeros
End With
End Sub

(I don't really like stuff that looks too much like magic!)



Jack Sons wrote:

Dave,

More or less related:

Every time I open a new wokbook zeros will be visibly represented, so I
always have to uncheck that option in toolsoptions etc.

In C:\program files\microsoft office\office\xlstart I put a "map1.xls" of
which all sheets have the zeros option unchecked. When I start Excel that
map.xls pops up, as expected, but for every extra sheet in that workbook
and
any new workbook I open the zeros option has to be unchecked again.

I simply want every new sheet of every new (and preferably also
excisting)
workbook to have the zeros option unchecked by default.

What should I do to accomplish that?

Jack Sons
The Netherlands

"Dave Peterson" schreef in bericht
...
Saved from a previous post:

Excel tries to help by remembering the last settings you used--except
for
the
first search in that session.

You can use that to your advantage.

You could make a dummy workbook and put it in your xlStart folder.
Have a
macro in that workbook that does a find (and sets all the stuff the way
you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes
to
get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Brian wrote:

When I execute Edit then Find, Excel begins to search by row. Can
anyone
suggest how:
(a) to directly change this default or
(b) to incorporate a few lines of VBA codes
so as to make it search by column without having to select Options
first,
and change the search command to "By Column"?
Thank you for your help.

Brian

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson