how to use the search dialog box ?

[cyrille]

Hello Everybody,
I have a question concerning the opening of dialogs box. I would like to
open the Search Dialog Box. My stupid code is like this :

Private Sub CommandButtonSearch_Click()
'ouvre la boite de dialogue Rechercher
Application.Dialogs(xlDialogFormulaFind).Show
End Sub

With this code, the problem is that the Search Dialog box is indeed open
but whatever the text to find in the worksheet, there is no result. If I
stop the macro, open by hand the Search Dialog box and type the same
text, it works... Can you help me to solve this problem ?
Thanks a lot in advance.
Cyrille

[Jim Rech]

Some or all Excel versions before Excel 2000 had this problem. When run
from a macro the way you're doing it Excel would only search the range
selected when the macro is run. This is usually just one cell. One way to
overcome this:

Private Sub CommandButton1_Click()
Cells.Select
Application.Dialogs(xlDialogFormulaFind).Show
ActiveCell.Select
End Sub

This was fixed in Excel 2002 and later.

--
Jim Rech
Excel MVP
"cyrille" > wrote in message
...
> Hello Everybody,
> I have a question concerning the opening of dialogs box. I would like to
> open the Search Dialog Box. My stupid code is like this :
>
> Private Sub CommandButtonSearch_Click()
> 'ouvre la boite de dialogue Rechercher
> Application.Dialogs(xlDialogFormulaFind).Show
> End Sub
>
> With this code, the problem is that the Search Dialog box is indeed open
> but whatever the text to find in the worksheet, there is no result. If I
> stop the macro, open by hand the Search Dialog box and type the same
> text, it works... Can you help me to solve this problem ?
> Thanks a lot in advance.
> Cyrille

[cyrille]

Jim Rech wrote:
> Some or all Excel versions before Excel 2000 had this problem. When run
> from a macro the way you're doing it Excel would only search the range
> selected when the macro is run. This is usually just one cell. One way to
> overcome this:
>
> Private Sub CommandButton1_Click()
> Cells.Select
> Application.Dialogs(xlDialogFormulaFind).Show
> ActiveCell.Select
> End Sub
>
> This was fixed in Excel 2002 and later.
>
Thanks a lot for your help !
Very nice to see such a compatibility ... ;-)