Excel97 - Application.SendKeys ("^{f}")

[LokiOfGameSlave]

Using Excel97 ... I have created a spreadsheet / database with a number of buttons to do different things.

One of those buttons I have assigned to a macro called "Find". The coding (placed in Module2) is as follows:

Sub Find()
Application.SendKeys ("^{f}&quot
End Sub

The coding I have for the button to call the macro (in Sheet1) is:

Private Sub btn_Search_Click()
Call Find
End Sub

When I execute the macro directly in Module2 it works perfectly. BUT, when I go in to Excel and click the button (the one that I have assigned to my FIND macro), nothing happens.

Any ideas as to why?

The reason that I don't just click <CTRL> + &quot;F&quot; is that this spreadsheet is for my mother who is NOT very computer literate. I am trying to make buttons for everything that she will need to do, to make life easier for her. I make each of the button's captions easy for her to know what they do (ie - Search for someone's Last Name), etc..

I hope that is enough information.

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[LokiOfGameSlave]

Ok I was given the coding that I was missing. It seems that SendKeys needs to have Sheet1 in &quot;Focus&quot;.

So the full, correct coding to make this work is:

Sub Find()
Range(&quot;B1&quot.Select
Application.SendKeys (&quot;^{f}&quot
End Sub

Hope this will help others out there. And thanks to Matt for helping me with this.

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Luis939]

I finally have the solution for you lol, but not really from me, someone posted to the thread I started, I tested the code and it does work, so let me just paste everything they said

chandlm (Programmer) May 16, 2003
Have you tried

Range(&quot;a1&quot.Select
SendKeys &quot;^f&quot;

The Range select will just give focus back to the sheet so that the sendkeys statement works.This should then bring up the find dialog box as if you have pressed Ctrl-F yourself.

Works fine for me in Excel 2000.

HTH

Matt

without the range, it wont work, hope that finally helps friend, tell us if it worked, 'til next time!!

 

[Loomah]

Hi
Just a little note
You can do away with the select-a-range part of your code by changing the TakeFocusOnClick property of your command button to False.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[LokiOfGameSlave]

Neat. Thanks for the tip Loomah. Less is best.

Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[LokiOfGameSlave]

If I want to only Search the Last Name column I still have to set the Range(&quot;a:a&quot.Select.

Now how would I get it to &quot;Unselect&quot; the Range AFTER I have completed the search?


Pronunciation: 'lO-kE
Function: noun
Etymology: Old Norse
Definition: a Norse god who contrives evil and mischief for his fellow gods

 

[Loomah]

Loki
You don't have to select the range to start with eg

With Range("A:A")
    MsgBox .Find(79, , , xlWhole, xlByRows).Address
End With

will return the address of a cell containing the value 79 from a list of numbers without selecting anything (though in this case the sheeet needs to be active!)

But to answer your question literally just select a different range - change the selection eg range(&quot;A1&quot.select

;-)

If a man says something and there are no women there to hear him, is he still wrong?