How to get Find dialogbox through VBA macro in Excel2007

  • Thread starter Thread starter Yogesh Gupta
  • Start date Start date
Y

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
 
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
 
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?
 
That gives me the same dialog as ctrl-f and the two lines of code that Yogesh
suggested.

Is my xl2007 special <vbg>?
 
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
 
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.

Yogesh said:
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
Click to expand...
 
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
 
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.



Yogesh said:
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
Click to expand...
 
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.
 
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
 
Whew! I feel better now!

Yogesh said:
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

Dave Peterson said:
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.
Click to expand...
Click to expand...
 
Ps. I think that this line is the most self documenting.

Application.Dialogs(xlDialogFormulaFind).Show

And I'd use that in my code.

Yogesh said:
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.
Click to expand...
 
Back
Top