Hey, welcome back to the Mr. Excel net cast, I am Bill Jelen. We start out with massive amounts of data and they say, ‘how we are going to analyze this?’ Well, let us fire up the activity table and see if we can solve this problem.
Hey welcome back to the Mr. Excel net cast, I am Bill Jelen. Well today’s net cast is actually about something called Built-in Dialogs in Macros but first of all, let me set up the problem, how this came about.
Once I go out to find a dialog box, I have 12 work sheets here and I am looking for a word on one the word sheets. I just use Ctrl-F and search for like the word ‘Test’. It says, ‘it can‘t find the data you are looking for.’ Well, I know it’s somewhere in this Workbook and so we have to select all sheets like click on January and then shift click on December, that selects all sheets and now Ctrl-F will find that example back here on the main work sheet.
Okay, 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 Find in all and let us just assign in to a shortcut key, so in this Workbook, okay.
So, the macro recorder is on, they recorded the action of going into group mode so selecting our worksheets. And then used Ctrl-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 where they could the search, it does not work that way.
Let me do Ctrl and I got a group mode, I’ll Ctrl-L and what it does it goes back in a group mode but never displays the Dialog Box.
There in the seminars, well you know I am pretty sure there is a good way to do this. What we have to do is use application.dialogs and then the Show method. So first of all, let us just—instead of hard quoting the sheets, we’ll just say Worksheets.Select, that will select all the Worksheets in the Workbook and then I have this grand plan, I said were going to use application.Dialogs which is a list of what I thought was all of the Excel Dialogs and then .show. So I started going through the list here and I feared up estimate here, it’s going to be xlDialog and then find or something like that so. I’m looking through the F’s and there are fine file, filter advanced, font, there is no font and then I said, all right, let’s come down to, maybe the color replace. So I went down to the R’s and Row Height, Routing Slip, Replace Font.
You know, these are all built-in dialogs 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 that’s kind of shut down. So I still have a solution although I will admit that it is a huge clue. We’re going to use application.SendtheKeys.
Now how do you get that find dialog box to display? You could use Ctrl-F, if you are in the old Excel, it would be Alt-F and then in the new Excel, having learned at home, find and select, I’m sure there are set of keys that could get us there but Ctrl-F will work. So a Ctrl key is a curve and then lower case up, we’ll send Ctrl-F and that should force the dialog box to display. Let’s go and try, come back here and select one of our sheets plus the shortcut key to run that Macro and perfect, we are now in group mode, it selected all the work sheets I have and displays the dialog box for it. Okay.
So, if you are 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.Dialogs. If you are lucky enough that your built-in dialog is in the list otherwise, you send keys, the character send to Ctrl upper sends them all 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 will see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services