Problems with application.Dialogs(xlDialogFormulaFind).Show

[lydya]

Hi,

I'm experiencing weird problems with this 'apparently' basic piece of code:
(where 'Sheet1' is the sheet CodeName)

Sub FindInSht1()
Sheet1.Activate
Columns(2).Select
Application.Dialogs(xlDialogFormulaFind).Show
End Sub

With Excel 2003:
It first worked fine until someone hit the 'Enter' key to run the search
(instead of using the mouse to click on the "find next" or "close button" of
the dialog box), then Excel crashed and sent that message suggesting to
report the error to Microsoft.
I tried several times from different computers and got the same error &
crash problem.

Now, even when it seems to work fine (when using the mouse to click on the
buttons rather than pressing the 'Enter' key), I noticed that after running
the macro, I cannot access any more to the Find dialog box using the
Edition/Find or Replace command or CTRL F, until I close Excel and launch it
again.

I tried the code with Excel 2002:
It doesn't cause Excel to crash (even using the 'Enter' key) but I still
can't access the Find command via the menu after running it.

Does anyone have an idea of what might be causing these surprising
problems??

--

By the way, I have another question: why can't I use
Sheet1.range("b:b").Select?? (using the codename)
It returns an error unless Sheet1 is the activesheet (but in this case, why
would I need to mention it?!)

Thank you for your help!

Lydya

 

[Jim Cone]

Lydya,

1. I have had poor results using sheet code names.
Suggest you try substituting the actual worksheet name
for the code name... Worksheets("Name")
or
use the worksheet index number... Worksheets(1)

2. To make a selection on a sheet, the sheet must be the active sheet.
Your code example does not make sheet1 the active sheet.

Jim Cone
San Francisco, USA

 

[lydya]

Hi Jim,

Thanks for your answer.

I have anyway tried the code using the sheet name instead of the code name
but I still got the same error & crash problem with the Find dialogbox
displayed by the macro when using the 'Enter' key instead of the click on
the box buttons...

Bye,

Lydya