How to get Find dialogbox through VBA macro in Excel2007

[Yogesh Gupta]

I am using Excel2007

I am looking for a VBA code to show excel find dialogbox that you get
through Ctrl+F.

I am not able to get the same dailogbox through excel VBA macro.

dialog box through following code is not similar to what you get with Ctrl+F

Application.Dialogs(xlDialogFormulaFind).Show

Another one which gives this dailog box does not work with auto open macro
Application.CommandBars.FindControl(ID:=1849).Execute

 

[Matthew Herbert]

Yogesh,

The Excel 2007 ribbon is a complete revamp of the menu bar from earlier
versions of Excel. There is a lot of information on the web regarding the
ribbon. Ron's (http://www.rondebruin.nl/tips.htm) and Stephen's sites
(http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm) have some good
information on manipulating and working with the ribbon.

In Excel 2007 you can call the find dialog with the following:

Application.CommandBars.ExecuteMso "FindDialogExcel"

Best,

Matthew Herbert

 

[Dave Peterson]

All 3 dialogs look the same to me (Ctrl-f and the 2 shown with your code).

Maybe you have to click the Options button to see them the same way????

Or maybe you're viewing a different Find dialog????

What's the difference that you see?

 

[Dave Peterson]

That gives me the same dialog as ctrl-f and the two lines of code that Yogesh
suggested.

Is my xl2007 special <vbg>?

 

[Yogesh Gupta]

Actual issue is that none of these lines work with Auto_open macro, I have
tested both the methods as below but none seems to work with autoopen

Following code is on the this workbook section and does not work.
Private Sub Workbook_Open()
Application.CommandBars.ExecuteMso ("FindDialogExcel")
Application.CommandBars.FindControl(ID:=1849).Execute
End Sub


Following code is in the module1 and does not work
Sub Auto_open()

Application.CommandBars.FindControl(ID:=1849).Execute
Application.CommandBars.ExecuteMso ("FindDialogExcel")

End Sub

Is there a way that I can get this as auto open macro with options expanded
with within workbook selected. Pls do let me know about the same if possible

 

[Dave Peterson]

What does not work mean?

Is it just that the options are not expanded on the dialog--or that the dialog
doesn't show up.

I'm guessing that you meant that the dialog appears, but the dialog doesn't open
with the Options displayed.

I don't know a way of showing that -- maybe you could experiment using the
accelerator key (t) and SendKeys. (I wouldn't bother because I don't trust
Sendkeys to do what I want.)

But I created a test workbook with this procedure in a general module.

Option Explicit
Sub Auto_open()

'Application.Dialogs(xlDialogFormulaFind).Show
'Application.CommandBars.FindControl(ID:=1849).Execute
'Application.CommandBars.ExecuteMso "FindDialogExcel"

End Sub

I saved it as book1.xlsm (macro enabled workbook. I uncommented each of the
lines one at a time and saved, close and reopened.

They all displayed the same dialog as I see when I hit ctrl-f.

I didn't test the workbook_open event.

Actual issue is that none of these lines work with Auto_open macro, I have
tested both the methods as below but none seems to work with autoopen

Following code is on the this workbook section and does not work.
Private Sub Workbook_Open()
Application.CommandBars.ExecuteMso ("FindDialogExcel")
Application.CommandBars.FindControl(ID:=1849).Execute
End Sub

Following code is in the module1 and does not work
Sub Auto_open()

Application.CommandBars.FindControl(ID:=1849).Execute
Application.CommandBars.ExecuteMso ("FindDialogExcel")

End Sub

Is there a way that I can get this as auto open macro with options expanded
with within workbook selected. Pls do let me know about the same if possible

 

[Yogesh Gupta]

By does not work I ment that in case of workbook open even it results into
error message as these lines are not valid VBA code in workbook open event.

Dilaog box which appears with following is not same as you get by Ctrl+F
Application.Dialogs(xlDialogFormulaFind).Show

Following does not show any dailog box when you open file.
Application.CommandBars.FindControl(ID:=1849).Execute
Application.CommandBars.ExecuteMso "FindDialogExcel"

It shows run-time error '-2147467259(80004005)':
Method'Execute' of object'_commandbarButton' failed

I understand from the post from you that it is working file on your machine
but this fails to execute on my machine.

What surpirises me is that after pressing the end button, if try to run it
from Run Macro dialog box, it works fine.

However I need it to work though Auto_open at the time of openng of workbook.

Regards

 

[Dave Peterson]

Since you want to work with the Auto_Open procedure, I'm not testing the
workbook_open event.

All three lines of code worked exactly the same for me and showed the same
dialog as I get with ctrl-f.

I don't have a guess why none of them works for you--and I still don't
understand the difference you're seeing.

By does not work I ment that in case of workbook open even it results into
error message as these lines are not valid VBA code in workbook open event.

Dilaog box which appears with following is not same as you get by Ctrl+F
Application.Dialogs(xlDialogFormulaFind).Show

Following does not show any dailog box when you open file.
Application.CommandBars.FindControl(ID:=1849).Execute
Application.CommandBars.ExecuteMso "FindDialogExcel"

It shows run-time error '-2147467259(80004005)':
Method'Execute' of object'_commandbarButton' failed

I understand from the post from you that it is working file on your machine
but this fails to execute on my machine.

What surpirises me is that after pressing the end button, if try to run it
from Run Macro dialog box, it works fine.

However I need it to work though Auto_open at the time of openng of workbook.

Regards

 

[Yogesh Gupta]

Thanks Dave for your time, now I realsed that macro security was casuing this
error, my machine has security setting
disable all macros with notification.
The macro was excuted when I clicked on enable this event but was resulting
into error. However when I changed the macro security to
enable all macros

It started working fine.

Thanks for giving your time.

 

[Yogesh Gupta]

Thanks Dave for your time, now I realised that macro security was casuing this
error, my machine has security setting
-disable all macros with notification.
The macro was excuted when I clicked on enable this event but was resulting
into error. However when I changed the macro security to
-enable all macros

It started working fine.

Thanks for giving your time

 

[Dave Peterson]

Whew! I feel better now!

Thanks Dave for your time, now I realised that macro security was casuing this
error, my machine has security setting
-disable all macros with notification.
The macro was excuted when I clicked on enable this event but was resulting
into error. However when I changed the macro security to
-enable all macros

It started working fine.

Thanks for giving your time
--
Yogesh Gupta

Since you want to work with the Auto_Open procedure, I'm not testing the
workbook_open event.

All three lines of code worked exactly the same for me and showed the same
dialog as I get with ctrl-f.

I don't have a guess why none of them works for you--and I still don't
understand the difference you're seeing.

 

[Dave Peterson]

Ps. I think that this line is the most self documenting.

Application.Dialogs(xlDialogFormulaFind).Show

And I'd use that in my code.

Thanks Dave for your time, now I realsed that macro security was casuing this
error, my machine has security setting
disable all macros with notification.
The macro was excuted when I clicked on enable this event but was resulting
into error. However when I changed the macro security to
enable all macros

It started working fine.

Thanks for giving your time.