Solved

Short Macro to auto load a CTRL Shortcut

Posted on 2014-07-19
16
147 Views
Last Modified: 2014-07-25
I'm trying to make a worksheet very simple for someone who doesn't know Excel.  So I want to program a function like "FIND" into a macro and then place a button on the worksheet where when pressed, up comes the FIND capability.

Here's what I tried to do;
Macro2()
Application.OnKey "^{+}F"
End Sub

But it doesn't work.

Any help would be appreciated.

B.
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
16 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206151
I tried the example please view the attachement of functionkey.xls
In the inputbox, type the search string such as "Joe" the "find"
action will react and the cell is selected
the sharp functionkey will call functionkey() when you click on it.



Why you mentions  "^{+}F", you want to hit ^+F instead of the mouse right ?

Duncan
functionkey.xls
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206155
Please see the new attachement file
with  the following code,

Private Sub Workbook_Activate()
    Application.OnKey "+^{F}", "functionkey"
End Sub

Open in new window


Now I know what you want

Ducan
functionkey.xls
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206157
I put the sub of  Workbook_Activate
at THisWorkbook sheet that will be run  automatically
when the file is re-open. The ctrl-F key will
activate to wait for user to press and call
the function of functionkey()

Duncan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:duncanb7
ID: 40206163
So now you can click sharp by right-click or cltr-F to call
macro of functionkey(). ANd the inputbox will show up
and type "Joe" search text , and th function will find the
cell location.

Hope understand your question completely.If not, please point it out

Duncan
0
 
LVL 13

Assisted Solution

by:duncanb7
duncanb7 earned 100 total points
ID: 40206219
Please take a look  the new attachment for reference only

Duncan
functionkey.xls
0
 

Author Comment

by:Bright01
ID: 40206328
Duncan,

Made the changes.  One last thing.... and I promise this isn't "scope creep"..... If I find a name, and then am below the next name I try to find, the macro doesn't start at the top again.  It starts where the active cell is.  So if "Fed Ex" is what I'm looking for, and I'm on the cell for "Ford", it can't find it because it is "above" the Ford cell/record.

Is there a line of code that makes it start from the top of the column every time you do a Find?

B.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206334
Do you think summarize what the last issue you have on the new thread that will be easier
for other experts to look into it ?


Duncan
0
 

Author Comment

by:Bright01
ID: 40206337
Normally I would say so; but I think we are talking about "tweeks" here.   Here are the two outstanding issues;

1.) The find function needs to start at the top every time it is used.
2.) Case should not matter.

If I need to author another question, I'm going to have to get someone up to speed on the great work you have already done.  The question has been authored twice already and you're running both results.

Make sense?

B.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40206342
Yes.

Duncan
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40206512
Bright01,

It appears you have a specialized application already going on with Duncan, but if you truly want to emulate the [Ctrl]+[F] action of calling up the Find dialog box, then you would use code like this instead:
Sub FindOnly()
    Application.Dialogs(xlDialogFormulaFind).Show
End Sub

Open in new window


I've attached a workbook that has two buttons:  one for "Find" and one for "Find and Replace".

Note:  Personally, I think that this type of handholding is a disservice to your novice Excel user.  Knowing the basic shortcut keys is essential to learning how to work with Excel and it's one of the very first things I teach to students (i.e., fellow employees).  I recommend the following resources to help you educate fellow users:

Excel 2003
http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-in-excel-2003-HP005203781.aspx?CTT=1

Excel 2007
http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx 

Excel 2010
http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-in-excel-2010-HP010342494.aspx?CTT=1

Microsoft even offers free online training on these.

Regards,
-Glenn
EE-OnKey.xlsm
EE-OnKey.xls
0
 

Author Comment

by:Bright01
ID: 40206574
Glenn,

Thanks for the commentary and the code/lesson.  My users very much like to view Excel as an application instead of a tool.  So when we put together data sets and they want to do certain functions, they don't want to learn anything about Excel.  Personally, I completely agree with you.  That said, I'm just trying to do the best job I can.

With the code you gave to me, is there a way to limit the search to a particular range or column/row?  Right now, if I use your Find capability, it will find the references on different worksheets.  I need to limit the search actually to one particular column.

Thank you and Duncan both for the help here.

B.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 400 total points
ID: 40206943
The example I provided would not offer any specialized find (i.e., in a specific range on a sheet); it merely duplicates the Find dialog.
0
 

Author Closing Comment

by:Bright01
ID: 40207256
Glenn and Duncan,

Really appreciate the work here.  I've integrated it into my production copy and it works great!  Again, "thank you!".

I did have one last question;  In order to limit the search to the current spreadsheet instead of the Workbook, can I put the code either in the Sheet itself (instead of a module) or in "ThisWorkbook" and it will limit its search to only that worksheet?

Again, thanks,

B.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40207534
Yes; you can use this alternate statement to call the combination Find/Replace box which limits the search to the current sheet by default:
Sub Find_Replace()
    Application.CommandBars.FindControl(ID:=1849).Execute
End Sub

Open in new window


I've updated the example workbook with this.

-Glenn
EE-OnKey.xls
0
 

Author Comment

by:Bright01
ID: 40207552
Glenn,

I added your new code (Find Replace) in the Module.... linked the macro back to my button.  It's still searching the entire Workbook instead of just the Worksheet I'm on.

Any ideas?

B.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40220387
Hi Bright01,

I re-opened the file I attached here and it is limiting the search to the Sheet (using the Find & Replace button/macro).  I don't know why yours is expanded to the Workbook.

-Glenn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question