Ctrl-A Fix for Excel 2003

G

Guest

I was able to whittle it down from all the help I got from you folks
(10/10/2007, thank-you-very-much) to something quite simple. (See below.)

I like to use control keys. I'm getting old and my right hand gets tired
from clicking all day long. (It's affecting my love life) <g>

For those of you who like to use the Ctrl-A key to Select All, don't get bit
by thinking pressing it twice is selecting the entire sheet. If your active
cell is one of those buggy cells directly to the North and West of your data,
(and your data extends beyond the visible rows and columns of your window) it
may LOOK like the entire sheet is selected, but it may not be.

If you happen to have a blank row or column in the middle of your data you
may not even be getting all your data, less yet the entire sheet.

I created the following macro from other people's suggestions. I thought I
had to bind the Ctrl-A key to it every time I opened a workbook (in
Workbook_Open) but I discovered something. When I exported the macro there
was an attribute attached to it by the VB IDE. Whenever I import that module
into a new workbook it gets automatically bound to the Ctrl-A key. Nice.

Copy and paste these lines into Notepad. Save As Module1.bas, then import
the module into any workbook to fix Ctrl-A. It won't work trying to paste
the lines into the VB editor because the attribute lines need to be read in
and stripped by the IDE.

Attribute VB_Name = "Module1"
Public Sub CtrlA()
Attribute CtrlA.VB_ProcData.VB_Invoke_Func = "a\n14"
'
Dim CellAddress As String
'
CellAddress = ActiveCell.Address
Cells.Select
Range(CellAddress).Activate
'
End Sub

P.S. you can always rename the module, or in my case, I included it with all
my utilities that I always load into any new workbook.

P.P.S. The command you can enter in the immediate window to bind the Ctrl-A
key, should you need to know is:

Application.MacroOptions Macro:="CtrlA", ShortcutKey:="a"

(there is a HasShortcutKey:=True parameter, but it doesn't seem to be
necessary. Also, the "a" must be lowercase.)

Later,
Charlie
 
G

Guest

Even though I don't use the keyboard controls very much, it was very nice of
you to come back with the information, Charlie. I am sure there are others
who will use it.
 

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

Top