VBA to get "Find and Replace"

[slack7639]

I would like to set a button that opens up "Find and Replace" to search "By Columns" . . . tired of clicking to do this everytime . . . please see screenshot:

Find and Replace (a jpg on my Google Docs) . . . https://drive.google.com/open?id=1POdfmAQ8Ac2Uqj1yMiep9x6KytQpzu53

Any idea what the VBA code is to do this? Thx

 

[Scott Huish]

What are you trying to do with this exactly? VBA wasn't exactly designed to do what you are asking. pgc01's solution will give you the functionality, you just don't see the option displayed but it is set. Mine uses a slightly different dialog box but shows the setting you are looking for.

You can use VBA to generate the functionality of the Find Box which you saw when you recorded the macro.

 

[slack7639]

I'm used to doing:

Ctrl+F (2 keys) / Options (1 click) / Search: "By Columns" (2 clicks)

I have figured out how to automate it, as seen above,

but, I'm used to seeing the "Find and Replace" box, with the "Options" showing.

With this macro, you have to click "Options" once, when initially opening it, then, they stay that way (shown), as long as you have Excel open.

I'm curious if there's a way to not have to click "Options" at all, and just have it open that way every time.

I have NirCmd installed, also. Maybe it could click options somehow?

 

[Scott Huish]

I understand that, but why do you have to show it that way if the functionality has already been achieved?

 

[slack7639]

I'm used to seeing it, and it seems do-able . . . it might even be easy . . . I just don't know the code

 

[slack7639]

Figured it out:

See how they underscore a letter in some of the words? That means, send . . . "Alt+" that letter . . . to activate it.
So, to see the "Options," send Alt+t

SendKeys ("%t"), True

 

[slack7639]

I put the cursor in various places . . . Sometimes you have to click it twice for it to work . . . I wonder what that's about

 

[slack7639]

Instead of clicking it twice, I see that it works consistently, if I put another "%t" in there:

SendKeys ("%t%t"), True

Do you think it misses it sometimes, because it goes to fast?

 

[slack7639]

UPDATE:

' I put the cursor in various places in the Worksheet, with just one "%t"
' Sometimes you have to click it twice for it to work. What's that about? [Alt+t is being sent again, which collapses it]
' I don't know, but instead of clicking it twice, I see that it works if I put in another "%t" in there, so I'll do that.

' SendKeys ("%t%t"), True

' The next day, this doesn't work.

' Try two of Alt+t's, with a second in between.
' VBA pausing by tenths of seconds (or milliseconds)
' . . . I think we have established in prior threads that Wait and OnTime functions only have a "resolution" of one second.
' . . . For finer control, I think you'll need to use API calls
' . . . https://bytes.com/topic/visual-basic/answers/738464-vba-pausing-tenths-seconds-milliseconds

' SendKeys ("%t"), True
' Application.Wait (Now + TimeValue("00:00:01"))
' SendKeys ("%t"), True

' Then, this didn't work . . . Is there some issue with Sendkeys not working?

' What if I put a wait of 1 second between the opening of "Find and Replace," and sending Alt+t?
' Fails. When you click it again, it collapses "Find and Replace" . . . Wait, that's what it's doing.

' What's going on is, the first time you open it, Alt+t works to expand "Find and Replace"
' . . . But click the macro a second time, and Alt+t collapses "Find and Replace"
' So, Alt+t is good to get it to open the first time, but since the behavior of this dialog is to remain how you set it throughout your Excel session,
' . . . the next time you click it, the macro collapses it.
' So, you could have one button that opens "Find and Replace," and another (without Alt+t) for your next use of "Find and Replace",
' . . . or, simply open it *without* Alt+t, and manually click "Options" to see that if you want it, or leave it collapsed (functionality is there),
' . . . and it will be how you set it for your Excel session - THIS SEEMS EASIEST, DO THIS