how to use the search dialog via a macro?

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,

Your code works for me. But if you have problems, you can use the find
method:

Sub TryNow()
Dim myCell As Range
Set myCell = Cells.Find(InputBox("What do you want to find"))
If Not myCell Is Nothing Then
MsgBox "That was found in cell " & myCell.Address
End If
End Sub

HTH,
Bernie
MS Excel MVP

"cyrille" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Thanks Bernie for your proposition.
Anyway, I would like to understand the reason why it is not working with
my code.. Just a clue (maybe for U...) : when the Search dialog box is
opened by the macro, all icons, for instance from the standard tools
bar turn to grey until I close the Search Dialog box AND I click one
cell into the worksheet. Could it be possible that the instruction :
>> Application.Dialogs(xlDialogFormulaFind).Show
is only showing the Dialog but not allowing to work with it ?
Just in case, I'm using Excel2000 SR1 (OS= XP Pro)
Thanks for all your expert advices.
Cyrille

Bernie Deitrick wrote:

> Cyrille,
>
> Your code works for me. But if you have problems, you can use the find
> method:
>
> Sub TryNow()
> Dim myCell As Range
> Set myCell = Cells.Find(InputBox("What do you want to find"))
> If Not myCell Is Nothing Then
> MsgBox "That was found in cell " & myCell.Address
> End If
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "cyrille" <[email protected]> wrote in message
> news:[email protected]...
>
>>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,

Everything should grey out, just as you describe, and stay greyed out, until
you press the close button.

Try this code. Select a cell other than the one you want to find, then click
your button. You should get the address of the cell you selected in a
message, then the dialog should appear after clicking OK. Do the search,
then click the close button on the dialog. You should then get the address
of the found cell in another message.

Private Sub CommandButtonSearch_Click()
MsgBox ActiveCell.Address
Application.Dialogs(xlDialogFormulaFind).Show
MsgBox ActiveCell.Address
End Sub

HTH,
Bernie
MS Excel MVP

"cyrille" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bernie for your proposition.
> Anyway, I would like to understand the reason why it is not working with
> my code.. Just a clue (maybe for U...) : when the Search dialog box is
> opened by the macro, all icons, for instance from the standard tools
> bar turn to grey until I close the Search Dialog box AND I click one
> cell into the worksheet. Could it be possible that the instruction :
> >> Application.Dialogs(xlDialogFormulaFind).Show
> is only showing the Dialog but not allowing to work with it ?
> Just in case, I'm using Excel2000 SR1 (OS= XP Pro)
> Thanks for all your expert advices.
> Cyrille
>
> Bernie Deitrick wrote:
>
> > Cyrille,
> >
> > Your code works for me. But if you have problems, you can use the find
> > method:
> >
> > Sub TryNow()
> > Dim myCell As Range
> > Set myCell = Cells.Find(InputBox("What do you want to find"))
> > If Not myCell Is Nothing Then
> > MsgBox "That was found in cell " & myCell.Address
> > End If
> > End Sub
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "cyrille" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>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
> >>
> >
> >
> >

Hi Bernie,
Thanks a lot! Now I understood that (at least with with Excel2000) one
search is applied to the whole worksheet when it is manually done (via
the menu) and only applied to previously selected cells when it is done
via a macro... I should have think about it !
Cheers !


Bernie Deitrick wrote:

> Cyrille,
>
> Everything should grey out, just as you describe, and stay greyed out, until
> you press the close button.
>
> Try this code. Select a cell other than the one you want to find, then click
> your button. You should get the address of the cell you selected in a
> message, then the dialog should appear after clicking OK. Do the search,
> then click the close button on the dialog. You should then get the address
> of the found cell in another message.
>
> Private Sub CommandButtonSearch_Click()
> MsgBox ActiveCell.Address
> Application.Dialogs(xlDialogFormulaFind).Show
> MsgBox ActiveCell.Address
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
> "cyrille" <[email protected]> wrote in message
> news:[email protected]...
>
>>Thanks Bernie for your proposition.
>>Anyway, I would like to understand the reason why it is not working with
>>my code.. Just a clue (maybe for U...) : when the Search dialog box is
>>opened by the macro, all icons, for instance from the standard tools
>>bar turn to grey until I close the Search Dialog box AND I click one
>>cell into the worksheet. Could it be possible that the instruction :
>> >> Application.Dialogs(xlDialogFormulaFind).Show
>>is only showing the Dialog but not allowing to work with it ?
>>Just in case, I'm using Excel2000 SR1 (OS= XP Pro)
>>Thanks for all your expert advices.
>>Cyrille
>>
>>Bernie Deitrick wrote:
>>
>>
>>>Cyrille,
>>>
>>>Your code works for me. But if you have problems, you can use the find
>>>method:
>>>
>>>Sub TryNow()
>>>Dim myCell As Range
>>>Set myCell = Cells.Find(InputBox("What do you want to find"))
>>>If Not myCell Is Nothing Then
>>>MsgBox "That was found in cell " & myCell.Address
>>>End If
>>>End Sub
>>>
>>>HTH,
>>>Bernie
>>>MS Excel MVP
>>>
>>>"cyrille" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>
>>>>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