Solved Excel Macro - Find & Replace - Open options automatically

Dell / Inspiron 620
June 15, 2016 at 10:56:40
Specs: Windows 64, 2.901 GHz / 8104 MB
In the Excel Find & Replace box there is an Options button,
looking to see if there is a macro that will automatically open the F&R box
and set the extended options to display as the default?

Hunted around the web and the best I can find is this:

http://excel.tips.net/T008801_Chang...

and this

http://excel.tips.net/T003170_Searc...

Both of which are close, but not exactly what I'm looking for.

I know I can just do a CTRL-F and click the Options button,
and Excel will retain the setting until I close the workbook.

I also tried doing a Record a Macro, but nothing was recorded.

Looking for something that will work automagically any time I open a workbook.

Thanks.

MIKE

http://www.skeptic.com/


See More: Excel Macro - Find & Replace - Open options automatically

Report •


✔ Best Answer
June 17, 2016 at 10:38:43
Iv come up with another way to do it but t isnt very elegant. I dont usually use sendkeys but i couldnt figure out any other way, yet.

So basically what the code does is

Opens the find and replace dialogue
Sends keys alt + t which expands the options
Sends key escape to close the dialogue

Private Sub Workbook_Open

Application.Commandbars("Edit").Controls("Replace...).Execute

Application.Sendkeys ("%+t")
Application.Sendkeys "{Escape}"

End sub






#1
June 15, 2016 at 21:30:57
I don't have access to Excel at the moment, so I'll just suggest a concept. If you think it will work for you, I can put something together, but not until Friday.

There's lots of pieces and parts here, so let me describe the pieces then I'll put the parts together.

1 - When you use the VBA .Find instruction, whatever options you choose within the macro remain set in the Find dialog box. If you don't explicitly set them within the macro, the .Find instruction will use whatever was set the last time the Find dialog box was used. This is one of the few directly correlated relationships between settings in VBA vs. Excel.

2 - Excel has an event macro known as Workbook_Open. This is code that will run when the workbook that contains the code is opened. The Workbook_Open event is a workbook level event, not an application level event, so the workbook that contains the code must be opened for the code to run.

3 - Any workbook that is stored in the xlstart folder will open automatically whenever the Excel application us opened. That is why many of us put all of our commonly used macros in a file called personal.xls and store the file in the xlstart folder. That way, whenever we open Excel, all of our macros are available to is.

4 - If an Excel file is hidden before it is saved, it will remain hidden the next time it is opened.

So, my thought is this:

We write a macro that attempts to "find" something on a worksheet and set all of the options inside that macro. We don't care if it actually finds what it is looking for, we just want the options set. We then hide that workbook and store it in the xlstart folder. Each time you open Excel, the hidden workbook will open, the code will run and the options will be set. We should be able to then automatically close that workbook, although it will close itself cleanly when you quit Excel since you won't have made any changes to it.

I think that will work, but I can't do anything until Friday. In the meantime, I would need to know what options you want set. Here are the choices:

https://msdn.microsoft.com/en-us/li...

Let me know what you think.

message edited by DerbyDad03


Report •

#2
June 16, 2016 at 07:13:35
Derby,

I had a go at this myself, only to see if I could do it, I managed to make it work (probably not as good as you can) but seeing as we have something called, AppSense Environment Manager at work, it keeps deleting the file from xlStartup, although I managed to make it work once by hard rebooting the machine.

   
Private Sub Workbook_Open() 
    Worksheets(ActiveSheet.Name).Columns().Replace _
    What:="Find This", Replacement:="Replace With", _
    SearchOrder:=xlByColumns, MatchCase:=False, lookat:=xlWhole
    
    Application.DisplayAlerts = False
    Application.Quit
    
End Sub

As you said, we need to change the values to match the requirement.

I am curious to see how you would have done this. One thing I couldn't figure out is how to make it change the values on each worksheet "Within: Sheet" to "Within: Workbook"?

message edited by AlwaysWillingToLearn


Report •

#3
June 16, 2016 at 07:57:48
DerbyDad,

Thanks for taking the time on this.

What I have begun to do now is:

Open Workbook
Press the CTRL-F keys to open the Find & Replace box
Click on the Options button, which expands the box to display all the options.
Click Close

That way, whenever I use CTRL-F or CTRL-H, the complete F&R box,
with all the options, displays.
It is a bit of an annoyancy to have to do this every time I
open a workbook, thus the request for a macro.

If we need to set it up so that the macro tries to find something first,
that's fine by me.

From what I have been reading and understanding about this
problem, it seems there is no easy way to simply "Click the Options button" so I'll leave the writing of the magical incantations to you. :-)

AlwaysWillingToLearn

Thanks for taking the time to write the macro.

Now to display my complete lack of VBA knowledge
Do I simply add it to my PERSONAL.XLSB file?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 16, 2016 at 08:46:42
AlwaysWillingToLearn

Tried your macro but no joy, does not seem to work.

I placed it in the PERSONNEL.XLSB, closed and saved,
but when I open a new workbook,
press CTRL-F,
the F&R box does not display the options.

Is there someplace specific I need to load the macro to?
It ended up in VBAProject (Personal.xlsb), Module6

MIKE

http://www.skeptic.com/


Report •

#5
June 16, 2016 at 08:59:41
Hi Mike

This code needs to go into 'ThisWorkbook' and the file should be save as a macro enabled workbook '.xmsm'

Then when you open the workbook it should change all the settings to whatever is set in the code.

Open excel
Go to the vba ide by pressing alt and f11
Double click 'ThisWorkbook' from project explorer
Copy paste the code

Save close
Then open to test

Make sure you're not pasting in module6 or any other, it must go into ThisWorkBook

message edited by AlwaysWillingToLearn


Report •

#6
June 16, 2016 at 10:22:38
OK, I opened an .xlsm workbook,
Pressed ALT-F11
Selected VBA Project (trip_tolls.xlsm)
Selected This Workbook under Microsoft Excel Objects
Pasted in the code
Saved everything and exited out of the workbook

Now when I try to open the workbook,
it opens for a split second, then exits out.
Can't get into the workbook.

Now what???

This is one of the reason I've always steered clear of VBA. :-(

MIKE

http://www.skeptic.com/


Report •

#7
June 16, 2016 at 10:42:44
Yup thats perfect iv told the cold to open then close, after setting up the parameters.

If you want the code to MOT close the workbook then you can place a ' infront of the line application.quit OR delete that line.

Basically what the code is doing, as Derby said, its doing a find and replace with the options from the options button specified in the code. Then it closes the workbook, saving your specifications so that the next time you open excel it has your settings saved..

So just delete or comment out the line application.quit

So in essence if you tell us what options from within the option settings you want to have, we can code them in so that when excel opens it presets them, i hope you get what i mean?

Sorry for any spelling mistakes im not very good on the iphone need a big screen and keyboard :)

message edited by AlwaysWillingToLearn


Report •

#8
June 16, 2016 at 11:07:46
if you tell us what options from within the option settings you want to have, we can code them in so that when excel opens it presets them

That's the point, I don't necessarily want to change the default settings,
I simply want the F&R extended options to be presented when I click CTRL-F

RIght now if I press CTRL-F I get a small pop up window with no options,
only an input box and four buttons, Options, Find All, Find Next, Close.

Hope that makes sense.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
June 16, 2016 at 11:10:46
Ahhhhh oh i see, sorry mate I assumed you wanted to change the options. So let me get this straight, when you open the find and replace window, you dont want to manually press the 'options'button you want it to automatically extend or open them?

Im not at a pc right now so will have a look tomorrow but hopefully derby will br around too as he is a lot more experienced than i.


Report •

#10
June 16, 2016 at 11:35:59
you dont want to manually press the 'options'button you want it to automatically extend or open them?

Correct, and you only need to do it once, because as long as your in the workbook, Excel remembers that you left it open with the options displaying.

Now how do I break into my trip_tolls.xlsm workbook and shut your macro off? It doesn't stay open long enough for me to do anything.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
June 16, 2016 at 11:58:26
AlwaysWillingToLearn,
I finally was able to break into the file.
Keep hitting CTRL-Break till I caught it just right. :-)

MIKE

http://www.skeptic.com/


Report •

#12
June 16, 2016 at 21:10:44
Mike,

I'm still not anywhere near Excel, but this might help you avoid the Ctrl-Break method.

http://www.jkp-ads.com/Articles/pre...

Shift-Open should work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
June 17, 2016 at 10:38:43
✔ Best Answer
Iv come up with another way to do it but t isnt very elegant. I dont usually use sendkeys but i couldnt figure out any other way, yet.

So basically what the code does is

Opens the find and replace dialogue
Sends keys alt + t which expands the options
Sends key escape to close the dialogue

Private Sub Workbook_Open

Application.Commandbars("Edit").Controls("Replace...).Execute

Application.Sendkeys ("%+t")
Application.Sendkeys "{Escape}"

End sub





Report •

#14
June 17, 2016 at 11:20:22
I'm getting a

Compile Error:

Syntax Error

And this line is highlighted and in Red:

Application.Commandbars("Edit").Controls("Replace...).Execute

I installed it in This Workbook, the same location as the previous one.

MIKE

http://www.skeptic.com/


Report •

#15
June 17, 2016 at 12:34:21
Mike,

It does not appear that you are the only one asking for this "feature" but I have yet to find an direct solution for the "Options" box.

Does this get you close to what you want? It doesn't offer all of the options available under the Option button, but maybe it provides those that you need.

Sub DifferentFindDialog()
  Application.Dialogs(xlDialogFormulaFind).Show
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
June 17, 2016 at 12:40:06
This post is a bit long.

First, this may sound strange, but I think there may be two different versions of F&R in Excel because:

When I open a workbook
Click Editing
Select Find

I get a popup window with an Input Box labeled Find What
with the box to the right of the label.
and Four button on the bottom, labeled, Options, Find All, Find Next, Close
and a Tab labeled Replace


Now when I run this Macro, a variation of AWTL's,

Private Sub Workbook_Open()

   Application.Dialogs(xlDialogFormulaFind).Show
   'Application.SendKeys ("%+t") < Commented out
   'Application.SendKeys "{Esc}" < Commented out

End Sub

I got a popup window with an Input Box labeled Find What
with the input box below the label
and Three button on the right side, labeled,
Find Next, Close, Replace
NO Tab labeled Replace
Two additional drop boxes on the lower left labeled Search: and Look In:
and two check boxes labeled:
Match Case
and Find entire cells only

Now this box is similar to the extended options box you get if you CTRL-F
then click options, but it is not the same.

The Find box you get using the Macro, kinda looks like it
might be a legacy version, from an earlier version of Excel maybe?

Also, I commented out the two Sendkey lines because they
did not seem to be working, as I get the same with them
commented out or not.

To make a long story short, what I eventually got to work
the way I wanted was this Macro:

Private Sub Workbook_Open()

   Application.CommandBars.FindControl(ID:=1849).Execute
   Application.SendKeys ("%+t")
   Application.SendKeys "{Esc}"

End Sub

Found the correct CommandBars line here:

http://www.mrexcel.com/forum/excel-...

There are four suggestions, one is the same as give by AWTL in his macro, but this one worked the best.

I want to thank AlwaysWillingToLearn for the inspiration
to get this working.
I do not know why your suggestion did not work.
From everything I've read it seems it should have.

Thanks again.

MIKE

http://www.skeptic.com/


Report •

#17
June 17, 2016 at 12:43:01
DerbyDad,

Thanks for the reply and suggestion, see my reply to AWTL.
Looks like I posted my reply after your post.

MIKE

http://www.skeptic.com/


Report •

#18
June 17, 2016 at 12:45:28
Absolutely brilliant mate glad you got it wotking, see your vba skills are not at slim as you say 😊 well done for preserving and getting the result you wanted and thanks for posting the working code, im going to try it out later on.

Report •

Ask Question