Hot Keys question

G

Greegan

I have recorded macros with a CTRL+Letter or CTRL+SHIFT+LETTER combination
for every combo except four. I'm running out of hotkeys.
Can anyone suggest something I can use in my macro that allows me to run the
macro with more hotkeys than I have?
Thanks in advance.
G
 
S

Steve Smallman

Greegan,

Sorry I don't have a specific answer for your question, however, I have a
suggestion to solve your problem.

Have you thought about using a customised menu? i.e. Adding a menu to
Excel's menu bar to contain all those macros?

if you are using them often, and have so many, you must have a large list of
what key combination does what. By adding the menu, you can simplify your
use, and if in a corporate environment, increase the useability of your
macros if/when you move on (OK, maybe that's not what you want, but at least
it will simplify your tasks....)

Steve
 
G

Greegan

Thanks Steve,
Yes I'm actually setting up a menu bar with a total of 5 menues and most of
them are on the list already.
But I was hoping someone could tell me how i could modify the macro so I
don't need to use the shortcut keys anymore.
I had asked this once before but we have since reinstalled XP and office.
Here is a sample of my macro. Keep in mind it is a basic one but useful to
what I do.

Also, its strange but when I create the menues in Office XP they are not
appearing in Office 97 or 2000 at work. I'm wanting to know if I have to
save the file to be compatible to 97-2003 as the save as drop list indicates
or is there an easier way?
Anywho, here is my macro, Thanks again


Sub AlphaCaseChange()
'
' AlphaCaseChange Macro
' Macro recorded 03/10/2004 by Scott Beattie
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Cells.Select
Selection.Replace What:="a", Replacement:="A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="b", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="c", Replacement:="C", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="d", Replacement:="D", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="e", Replacement:="E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="f", Replacement:="F", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="g", Replacement:="G", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="h", Replacement:="H", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="i", Replacement:="I", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="j", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="k", Replacement:="K", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="l", Replacement:="L", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="m", Replacement:="M", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="n", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="o", Replacement:="O", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="p", Replacement:="P", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="q", Replacement:="Q", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="r", Replacement:="R", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="s", Replacement:="S", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="t", Replacement:="T", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="u", Replacement:="U", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="v", Replacement:="V", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="w", Replacement:="W", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="x", Replacement:="X", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="y", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="z", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub
 
G

Greegan

does anyone have any more suggestions on this?
Greegan said:
Thanks Steve,
Yes I'm actually setting up a menu bar with a total of 5 menues and most
of them are on the list already.
But I was hoping someone could tell me how i could modify the macro so I
don't need to use the shortcut keys anymore.
I had asked this once before but we have since reinstalled XP and office.
Here is a sample of my macro. Keep in mind it is a basic one but useful to
what I do.

Also, its strange but when I create the menues in Office XP they are not
appearing in Office 97 or 2000 at work. I'm wanting to know if I have to
save the file to be compatible to 97-2003 as the save as drop list
indicates or is there an easier way?
Anywho, here is my macro, Thanks again


Sub AlphaCaseChange()
'
' AlphaCaseChange Macro
' Macro recorded 03/10/2004 by Scott Beattie
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Cells.Select
Selection.Replace What:="a", Replacement:="A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="b", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="c", Replacement:="C", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="d", Replacement:="D", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="e", Replacement:="E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="f", Replacement:="F", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="g", Replacement:="G", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="h", Replacement:="H", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="i", Replacement:="I", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="j", Replacement:="J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="k", Replacement:="K", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="l", Replacement:="L", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="m", Replacement:="M", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="n", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="o", Replacement:="O", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="p", Replacement:="P", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="q", Replacement:="Q", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="r", Replacement:="R", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="s", Replacement:="S", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="t", Replacement:="T", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="u", Replacement:="U", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="v", Replacement:="V", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="w", Replacement:="W", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="x", Replacement:="X", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="y", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="z", Replacement:="Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub
Click to expand...
 
G

Gord Dibben

How do you create the menus?

If by Tools>Customize and dragging menu items to the worksheet menu bar then
these customizations are saved in your Excel.XLB file and not with the
workbook.

Others won't have access to this *.XLB file so customized menus won't show up.

If you assign macros to a bunch of buttons onto a new Toolbar, you can attach
the Toolbar to the workbook.

Best to create menus "on the fly" with code in the workbook.

The sites below give example code and tips.

Note: some sites say for XL97, but same for 2003

http://support.microsoft.com/default.aspx?scid=kb;EN-US;830502

http://support.microsoft.com/support/kb/articles/Q159/6/19.asp

http://support.microsoft.com/support/kb/articles/Q162/8/78.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q166755&


Gord Dibben Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hotkey in proper workbook 2
VBA word change keys 0
How to open multiple instances of Word 365? 5
Setting language of a selected text in Office apps. 0
Improving Macro That Keeps First Letter 0
WORD VBA Search and switch to advanced ... 1
Slideshow with black border around it 1
Macros will not run with shortcut keys 2

Top