Short Macro to auto load a CTRL Shortcut
Asked by Bright01
at 2024-07-19 04:06:51
Point:500 Replies:16 POST_ID:829028USER_ID:11896
Topic:
Microsoft Excel Spreadsheet Software;;
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.
Here's what I tried to do;
Macro2()
Application.OnKey "^{+}F"
End Sub
But it doesn't work.
Any help would be appreciated.
B.
Author: Bright01 replied at 2024-07-20 05:09:04
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.
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.
Accepted Solution
Expert: Glenn Ray replied at 2024-07-19 23:15:01
400 points EXCELLENT
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.
Author: Bright01 replied at 2024-07-19 12:52:12
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.
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.
Expert: Glenn Ray replied at 2024-07-19 11:19:20
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:
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:
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
Expert: duncanb7 replied at 2024-07-19 08:15:12
Yes.
Duncan
Duncan
Author: Bright01 replied at 2024-07-19 08:06:27
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.
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.
Expert: duncanb7 replied at 2024-07-19 08:03:37
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
for other experts to look into it ?
Duncan
Author: Bright01 replied at 2024-07-19 07:54:23
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
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
Assisted Solution
Expert: duncanb7 replied at 2024-07-19 05:42:31
100 points EXCELLENT
Please take a look the new attachment for reference only
Duncan
Duncan
Expert: duncanb7 replied at 2024-07-19 04:34:40
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
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
Expert: duncanb7 replied at 2024-07-19 04:27:36
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
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
Expert: duncanb7 replied at 2024-07-19 04:25:33
Please see the new attachement file
with the following code,
with the following code,
Now I know what you want
Ducan
Expert: duncanb7 replied at 2024-07-19 04:19:21
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
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
