Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well today's netcast is actually about something called Built-in dialogues in Macros, but first of all let me set up the problem how this came about.
Wanna talk about the Find dialog box. I have 12 worksheets here, and I'm looking for a word on one of the worksheets.
If I just use control+F, and search for like the word Test. It says can't find the data you're looking for.
Well, I know it's somewhere in this workbook, and so, we have to select all sheets.
I click on January and then shift click on December, that selects all sheets.
And now, control+F will find that example back here on the May worksheet.
Ok so, I was doing a seminar and someone had tried to use the Macro recorder to solve this problem.
They said we turned on the Macro recorder, record new Macro and we'll call it, FindInAll and let's just assign that to a shortcut key.
Start in this Workbook OK.
So, the Macro record is on.
They recorded the action of going into group mode.
So, selecting all worksheets and then use control+F and they hoped to stop.
the Macro recorder right at this point and in excel 2007, it will actually stop.
But unfortunately, their goal was that the Macro would go into group mode and then display the dialog box.
So, that way they could do the search. It doesn't work that way, let me do control, and will get out of group mode.
I'll do control+L and what it does is, it goes back in the group mode but never displays the dialog box.
There in the seminar said well, you know I'm pretty sure there's a good way to do this.
What we have to do is use application.dialogues and then the show method.
So, first of all let's just instead of hard coding the sheets will just say worksheets.select that will select all the worksheets in the workbook and then I had this grand plan, I said we're going to use application.dialogues.
Which is a list of what I thought was all of the Excel dialogues and then .show.
So, I started going through the list here, and I figured OK it's going to be Excel dialogue and then find or something like that.
So, I'm looking through the F's and you know, there's FindFile, FilterAdvanced, Font. There is no Find, and then I said all right let's come down to maybe they call it replace.
So, I went down to the R's and Row Height, RottingSlip, ReplaceFont. You know these are all built-in dialogues and we could show them using this line of code, but Microsoft is just causing problems here because for whatever reason they decided not to put the find dialog in this list. It's not available, and so it's kind of shut down.
So, I still have a solution although I will admit that it is a huge code, we're going to use application.SendKeys.
I mean, how do you get the find dialog box to display you could use?
control+F, if you're in the old Excel at the Alt+E, app in the new Excel I haven't even learned at Home, Find and Select.
I'm sure there's a set of keys that could get us there but control+F will work.
So, a control key is a carrot and then lower case f, will send control+F and that should force the dialog box to display.
Let's go to try come back here and select one of our sheets press the shortcut key to run that Macro and perfect.
We're now in group mode it selected all the worksheets that I have and displays the dialog box for me okay.
So, if you're running a Macro and you want to get the dialog box to display.
The Macro recorder is not going to do that for you.
You can however use application.dialogues.
If you're lucky enough that you're built-in dialogue is in the list otherwise you send keys the carrot sends a control, a percent sends an old.
So, you can replicate just about any set of keys you would need to and get that dialog box to display.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well today's netcast is actually about something called Built-in dialogues in Macros, but first of all let me set up the problem how this came about.
Wanna talk about the Find dialog box. I have 12 worksheets here, and I'm looking for a word on one of the worksheets.
If I just use control+F, and search for like the word Test. It says can't find the data you're looking for.
Well, I know it's somewhere in this workbook, and so, we have to select all sheets.
I click on January and then shift click on December, that selects all sheets.
And now, control+F will find that example back here on the May worksheet.
Ok so, I was doing a seminar and someone had tried to use the Macro recorder to solve this problem.
They said we turned on the Macro recorder, record new Macro and we'll call it, FindInAll and let's just assign that to a shortcut key.
Start in this Workbook OK.
So, the Macro record is on.
They recorded the action of going into group mode.
So, selecting all worksheets and then use control+F and they hoped to stop.
the Macro recorder right at this point and in excel 2007, it will actually stop.
But unfortunately, their goal was that the Macro would go into group mode and then display the dialog box.
So, that way they could do the search. It doesn't work that way, let me do control, and will get out of group mode.
I'll do control+L and what it does is, it goes back in the group mode but never displays the dialog box.
There in the seminar said well, you know I'm pretty sure there's a good way to do this.
What we have to do is use application.dialogues and then the show method.
So, first of all let's just instead of hard coding the sheets will just say worksheets.select that will select all the worksheets in the workbook and then I had this grand plan, I said we're going to use application.dialogues.
Which is a list of what I thought was all of the Excel dialogues and then .show.
So, I started going through the list here, and I figured OK it's going to be Excel dialogue and then find or something like that.
So, I'm looking through the F's and you know, there's FindFile, FilterAdvanced, Font. There is no Find, and then I said all right let's come down to maybe they call it replace.
So, I went down to the R's and Row Height, RottingSlip, ReplaceFont. You know these are all built-in dialogues and we could show them using this line of code, but Microsoft is just causing problems here because for whatever reason they decided not to put the find dialog in this list. It's not available, and so it's kind of shut down.
So, I still have a solution although I will admit that it is a huge code, we're going to use application.SendKeys.
I mean, how do you get the find dialog box to display you could use?
control+F, if you're in the old Excel at the Alt+E, app in the new Excel I haven't even learned at Home, Find and Select.
I'm sure there's a set of keys that could get us there but control+F will work.
So, a control key is a carrot and then lower case f, will send control+F and that should force the dialog box to display.
Let's go to try come back here and select one of our sheets press the shortcut key to run that Macro and perfect.
We're now in group mode it selected all the worksheets that I have and displays the dialog box for me okay.
So, if you're running a Macro and you want to get the dialog box to display.
The Macro recorder is not going to do that for you.
You can however use application.dialogues.
If you're lucky enough that you're built-in dialogue is in the list otherwise you send keys the carrot sends a control, a percent sends an old.
So, you can replicate just about any set of keys you would need to and get that dialog box to display.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
