how to use the search dialog via a macro?

[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

 

[Bernie Deitrick]

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]

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 :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]

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]

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 !