Category : Lotus and other Spreadsheets
Archive   : LTSTIPS2.ZIP
Filename : KEYPAD.123

Numeric Pad Macro
(PC Magazine Vol 4 No 7 April 2, 1985 Spreadsheet Clinic)

The macro below is a good solution for using the numeric pad for
numbers and cursor movement while using 123. You must name four
ranges. The first line is named \N and starts the macro by hitting
Alt-N. The second line is named Numcont for the looping and
continuation of the macro. The third range is the Numentry menu. The
fourth range is called Scratch and can be put anywhere out of the way.
The Scratch range is used in the reminder messages at the start and end
of the macro so your response doesn't overwrite the last number entered.
After you invoke the program with Alt-N, it alternates between two
states. First, it prompts you and waits for you to input a value into
the cell where the cursor is. Second, it presents a menu that has all
choices beginning with numerals (cells A18 to H18). Thus, since you
can pick any 123 menu item by typing its first character, you can leave
the numeric pad in NumLock and use it both for data entry and menu
selection. Menu choices 2, 4, 6, and 8 correspond to single cursor
moves, whereas 3, 7, and 9 let you move two cells at a time. The 5 key
has been designated to halt the macro.

A7: '\N
A8: ;/x1Turn on the NumLock key then hit enter~SCRATCH~
A9: '/xgNUMCONT~
A12: 'NUMCONT
A13 '/xnEnter a number in the current cell:~~
A14 '/xmNUMENTRY~
A17 'NUMENTRY
A18: ^8
B18: ^6
C18: ^2
D18: ^4
E18: ^5
F18: ^3-Double Down
G18: ^9-Right Twice
H18: ^7-Left Twice
A19: 'Use cursor keys to move, 5=QUIT, 3=DOWN TWO, 9=RIGHT TWO, 7=LEFT TWO
A20: '{up}
B20: '{right}
C20: '{down}
D20: '{left}
E20: '/x1Turn off NumLock then hit enter~SCRATCH~
F20: '{down}
G20: '{right}
H20: '{left}
A21: '/xgNUMCONT~
B21: '/xgNUMCONT~
C21: '/xgNUMCONT~
D21: '/xgNUMCONT~
E21: '/xq
F21: '{down}
G21: '{right}
H21: '{left}
F22: '/xgNUMCONT~
G22: '/xgNUMCONT~
H22: '/xgNUMCONT~

The named ranges are: \N (cell A8), NUMCONT (A13), the menu, NUMENTRY
(A18), and SCRATCH (A24, or anywhere out of the way). The worksheet is
printed with the Cell-Formulas option for readability.

-----------------------------------------------------------------
Cursor Movement a' la WordStar
(PC Magazine Vol 4 No 17 Aug 20, 1985 Spreadsheet Clinic)

123 users often complain about having to switch between cursor
movement and number entry when using the numeric keypad. There are
macro approaches to the problem, and one of the easiest solutions
(PC Vol 4 No 7 -- above) is to use the shift key to switch back and
forth between number entry and cursor movement.
However, if you use WordStar, the macro below lets you use the
word processing commands to move the cursor while you are in NumLock.
Unlike WordStar, you use the Alt key rather than Ctrl. Also, {Home}
is assigned to the W key and {End} to the Z key, but otherwise the
commands are just like WordStar's. If you save this macro as
CURSOR.WKS, you can read it into any current worksheet with the /File
Combine Copy Entire CURSOR command. The \A portion of the macro is an
installation program that will assign the cursor movement macros to the
proper keys. Activate it by putting the cursor in the cell with \A and
typing /Range Name Labels Right A. Note that the
installation program erases itself after it has done the job.
Editor's Note: I like the installation program better than the
macro. For readers unfamiliar with the /Range Name Labels routine, its
purpose is to use labels in a column or row as range names for adjacent
cells. Using this command to attach macro labels is clever. However,
in the installation section of the macro, rather than proceed down the
column of macro labels, attaching each to its adjacent cell, it would
be easier to attach the lot with one command. Thus after the first five
{down}s in the macro, you could replace most of the rest of it with:
/RNLR{down}{down}{down}
{down}{down}{down}{down}
{down}{down}{down}{down}
{down}{down}{down}
The macro would be handier still if it were possible to make the
installation program an autoexecuting macro that would run as soon as
CURSOR.WKS was combined with your current file. However, the /File
Combine command disables range names in the file that's read in from
disk, so the installation macro will autoexecute only when you read it
in with /File Retrieve.
The macro:

A B

\A {DOWN}{DOWN}{DOWN}{DOWN}{DOWN}/RNLR~{DOWN}{DOWN}/RNLR~
{DOWN}{DOWN}/RNLR~{DOWN}{DOWN}/RNLR~{DOWN}{DOWN}/RNLR~
{DOWN}{DOWN}/RNLR~{DOWN}{DOWN}/RNLR~{DOWN}{DOWN}/RNLR~
{GOTO}\A~{LEFT}/RND\A~/RE{DOWN}{DOWN}{DOWN}{DOWN}{RIGHT}~

\E {UP}

\X {DOWN}

\S {LEFT}

\D {RIGHT}

\R {PgUp}

\C {PgDn}

\W {HOME}

\Z {END}

-----------------------------------------------------------------
A New Set of Keys
(PC Magazine Vol 4 No 6 Aug 6, 1985 Spreadsheet Clinic)

One way to use the numeric keypad to enter a column of numbers is
to use a program like ProKey or SmartKey to redefine the Enter key as
the down-arrow key. That way, you hit NumLock to activate the numeric
keypad, and then hit Enter after every number. You won't need to hold
down the shift key to move the cursor or fool with macros. And if you
need to put numeric data in rows you can always redefine Enter as the
right-arrow key.
Editor's Note: You shouldn't deactivate the Enter key, even if
only temporarily. Why not redefine the PrtSc key as the down-arrow key
instead? PrtSc doesn't do much for you in 123 anyway, and this
alternative would preserve the Enter key.

-----------------------------------------------------------------
The Last Word on NumLock
(PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic)

...you have already written about shifting back and forth between
numeric entry and cursor movement by holding down the shift key (PC Vol
4 No 7 above). For those of us who can't walk and chew gum at the same
time, I have written a macro that allows single-handed data entry using
the numeric keypad:
\A {?}{down}/xg\A~
To use it, hit NumLock and then run the macro with Alt-A. You can now
use the numeric keypad to enter numbers. Every time you hit the Enter
key, the number will appear in the cell, and the cursor will move to
the cell below.
If you make a mistake or want to move to a different part of the
spreadsheet, hit NumLock and use the cursor control keys to move into
position. The macro will still be waiting for your number and will
move to the next cell as soon as you hit Enter. If you work with rows
of numbers, you can set up another macro just as easily that moves you
to the right every time you hit the Enter key.

-----------------------------------------------------------------
1-2-3 and the PC's Number Pad
(PC World March 1985 The Help Screen by K. Koessel)

123's keyboard macros enable you to assign a series of keystrokes
and commands to an -character combination, such as -A. When
you press -A, 123 executes the assigned keystrokes and macro
commands automatically. You can assign characters, cursor movements,
the key, and even function keys to a keyboard macro. You can
also invoke a command that causes the macro to wait for input from the
keyboard before it proceeds with the remainder of the steps; yet
another command transfers control to another macro. Experienced users
can create highly complex keyboard macros (see "/X Marks the Macro,
PC World, Vol 2 No 3).
Load 123 and move the cursor to cell D1. (In an actual worksheet
you'll want to move the cursor to any empty cell that has a few blank
cells below it.) Enter the following lines into cells D1 through D3:
{?}
{down}
'/xg\a~
Now move the cursor back to cell D1. Use the /Range Name Create command
sequence to name the macro \a (a macro range must be named with a
backslash followed by a single letter). When asked to enter the range
to be named, enter the coordinates of the macro's first cell, D1.
To use the macro, move to the cell in which you'd like your list
of entries to begin. Start at cell A1 for this example. Press
, then -A, enter a number on the numeric keypad, and
press . The number is entered into cell A1, and the cursor
moves down to the next cell, awaiting the next number. When you've
finished entering your list of numbers, press and then
- to activate the cursor keys and exit the macro. Any
time you want to enter a list of numbers (or labels or formulas), just
move to the cell that will contain the first number and press ,
then -A. 123 does not include a macro key symbol for ,
, or (ScrollLock>.

-----------------------------------------------------------------
NumLock Key in 1-2-3
(PC World July 1985 Star-Dot-Star)

This solution is offered to the problem of using the PC's numeric
keypad in 123 in response to the question in The Help Screen ("1-2-3
and the PC's Number Pad," March 1985) (above). Many worksheets require
the ability to move the cursor to random cells and enter values at
those locations. To accomplish both, put the keypad numbers into a
macro that uses a "sticky" menu as shown in KEYMAC.
With NumLock on, this macro causes the number keys to move the
cursor in the usual manner until the Enter key is pressed. After that,
the number keys enter numbers until the Enter key is pressed again;
then the keys switch back to controlling the cursor. The only way to
escape the macro is to press Ctrl-Break. However, if you are willing
to give up one of the cursor movements, you can substitute another
command to return you to a previous menu.
To create the macro, type in the cell values as shown. Note that
the numeric digits must be entered as labels. Move the cursor to cell
A1 and create a range named CURSOR. (You can make just cell A1 the
range, but if the range covers the entire macro you can easily copy the
macro to other worksheets using the File Combine Copy Named Range
command.) Go to cell B8 and create a range named DIRECTION.
If you access this macro by way of another menu, then you're all
set. To access the macro with an Alt-letter combination, go to cell
A4 and create a range named \M (or another letter of your choice).

KEYMAC: Macro to alternate between cursor and numeric keys in 123

A B C D E F G

1 Enter data 1 2 3 4 6 8 9
2 Data Entry End Down PageDown Left Right Up PageUp
3 {?}~ /xm {down} {PgDn} {Left} {Right} {Up} {PgUp}
4 /xmCURSOR~ DIRECTION /xm /xm /xm /xm /xm /xm
5 ~ CURSOR CURSOR CURSOR CURSOR CURSOR CURSOR
6 ~ ~ ~ ~ ~ ~
7
8 2 4 6 8
9 Down Left Right Up
10 {End} {End} {End} {End}
11 {Down} {Left} {Right} {Up}
12 /xm /xm /xm /xm
13 CURSOR CURSOR CURSOR CURSOR
14 ~ ~ ~ ~

-----------------------------------------------------------------
Macrophobia (or, Keep Those Cards and Letters Coming)
(PC World, June 1985, The Help Screen by K. Koessel)

Reader response to "1-2-3 and the PC's Number Pad" in the March
issue has been enormous, proving, perhaps, that 123 macros are a hot
topic. Numerous correspondents noted that the key causes the
numeric keypad to alternate between its number and cursor functions.
Many letters also suggested that in light of such a simple solution to
Jim Hawkinson's proglem of entering columns of figures into a 123
worksheet, my macro response revealed a penchant for "lengthy and
difficult solutions to elementrary problems." Or it did not "indicate
a thorough knowledge of the product." Or it was "more complicated"
than simply enlisting an idle left hand to work the key.
True, the key can be used instead of the key to
access the nine numerals on the number pad (see "Number Pad vs. Cursor
Keys," The Help Screen, PCW, Vol 1 No 7); however, the technique is
inadequate for sessions that require quick entry of large amounts of
numeric data. The macro solution is largely intended for those familiar
with the number pad; these users tend to treat the left hand as a
placeholder on the hard copy that contains the date bound for 123. The
macro solution also demonstrates that 123 macros need not be long or
complex, are easy to implement, and, above all, needn't be feared as
being beyond one's capability simply because they're called macros.
That said, it's true macros are not as easy to create and save
with 123 as they are with some dedicated macro processing programs,
such as ProKey. Maintaining a library of 123 macros, perhaps in a
worksheet file called LIBRARY.WKS, is a good way to keep track of your
finished products. Sensible organizational habits within the library
file will help refresh your memory when you look up an old macro.
The sample below shows a portion of such a file. This segment
contains the definition for -T, a macro that automates setting and
clearing worksheet titles (/Worksheet Titles Set and /Worksheet Titles
Clear). For purposes of legibility, the worksheet has been set to a
global column width of 13, using /Worksheet Global Column-width 13. A
cell filled with asterisks (cell A100) separates each macro in the
library file and clearly distinguishes the beginning and end of each
macro. On this line in column C is a name describing the macro. Below
the first line, column A contains labels; column B, macro routines; and
column D, comments describing the actions that the macro routines
produce. The -T macro contains several routines; a blank line
marks the beginning and end of each routine.
In some instances, macros may be invoked only after certain
conditions have been met. The macro in "1-2-3 and the PC's Number Pad"
requires that the pointer be positioned at the first cell to receive
data before the macro can begin its work. For the -T macro, the
cell to the right of and below the columns and rows containing the
worksheet's titles must be given the range name Title Corner before the
macro is invoked. It's a good idea to keep a reminder of such
prerequisites at the top of the macro's comment column (see cell D101).
Because the action of a macro generally proceeds downward until it
reaches a nonlabel (empty, numeric or fomula) cell, assigning a range
name to the first cell of reach routine enables such a name to direct
the flow of the macro to various routines. In the -T macro each
label in column A is the range name for the cell to its right. You can
assign the names all at once by going to cell A101: press (the
{GoTo} key), type A101, and press . Then invoke the /Range Name
Labels Right sequence, hold down the key until the range
A101..A118 is marked, and press .
The -T macro is a "toggling" macro, which means that each
time it is invoked, it alternates, or toggles, between functions. In
this case, when invoked for the first time, it sets titles; when it is
invoked again, it clears titles. Toggling macros can be quite handy
but should be implemented with care because they modify themselves
(that is, they write to the worksheet) in order to switch functions.
You can keep toggling macros simple by following the general
format in the sample. The two functions between which the macro
alternates are kept as separate routines, each with a label to the left
of the first cell of the routine. Below these labels and their
associated functions are two single-line macro commands and their
labels. Both these single-line commands are /XGrange~. In one of the
single-line commands, range is the label for the first function (Set
Titles); in the other single-line commands, it's the label for the
second function (Clear Titles).
Because column B ranges have previously been named using the
labels in column A, you can refer to ranges by their range names.
Accordingly, when -T is invoked, control passes to the macro
command in the cell to the right of the label \T. This cell contains
a copy of one of the single-line commands, in this case /XGset titles~.
This command tells 123 to continue macro processing at the first cell
in the range named Set Titles. The last line of the Set Titles routine,
/Ctoggle2~\T~, tells 123 to copy the range named Toggle 2 (the other
single-line command) to the range named \T, overwriting the contents of
that cell. When -T is invoked again, its first command will be
/XGclear titles~, which tells 123 to continue macro processing at the
first cell in the range named Clear Titles. That routine ends by
copying the range named Toggle1 (the first single-line command) to the
range named \T. The -T macro has now been toggled twice and is
back to its original mode.
The -T macro illustration makes use of two additional
techniques. First, macros should always delete a range name before
reassigning the name to another range. In the Set Titles routine, the
first line invokes a /Range Name Delete sequence, freeing the range
name Current Cell before the second line assigns that name to the cell
the pointer currently occupies. Simply reassigning a range name will
not suffice; doing so will erroneously alter formulas dependent upon
that range name. In addition, you should assign a dummy range to range
names the macro will be using. This precludes the "Range name does not
exist" error when the macro tries to delete a range name. Such an
error would halt the macro before its work is done.

Sample:

A B C D E
100 ************ Titles toggle
101 \T /XGset titles~ Before calling, /RNCtitle corner~
102
103 Set titles /RNDcurrent cell~ Free this range name
104 /RNCcurrent cell~ Name current cell
105 {Home} Go Home to pull titles into view
106 {GoTo}title corner~ Go to cell right and below titles
107 /WTB Set both horiz. and vert. titles
108 {GoTo}current cell~ Return to current cell
109 /Ctoggle2~\T~ Copy `Toggle2' macro to \T
110
111 Clear titles /WTC Clear titles
112 /Ctoggle1~\T~ Copy `Toggle1' macro to \T
113
114 Toggle1 /XGset titles~ Continue macro at `Set Titles'
115
116 Toggle2 /XGclear titles~ Continue macro at `Clear Titles'
117
118 Current cell Dummy range Prevents `Range name does not
119 exist' on 1st invocation
120