I feel sort of foolish right now.

I couldn't get my computer to boot up properly this morning. It would go through the POST, get to the password login, and then when I put in the password and hit the Enter key, it went into routine shutdown mode and turned off.

I went through this process a couple of times, wondering how I was going to debug the problem when I couldn't even log in, and then it dawned on me...

I have a macro-programmable keyboard, and last night I had re-programmed it so that in an Excel spreadsheet when I put data into a selected cell, the Enter key would make the text bold, change it to 14-pt, change the color to red then save the changes and proceed to the next cell. Apparently something in the macro (you try coming up with a keyboard-only routine that will do that reliably )* also doubled as a computer shut-down command when I was at the password login screen.

Oops...

It is an interesting spreadsheet... just one of the 9546 different formulas in it goes like this:

=IF(RIGHT(G4,2)="ar",LEFT(G4,LEN(G4)-2)&"áramos", IF(RIGHT(G4,2)="er",LEFT(G4,LEN(G4)-2)&"iéremos", IF(RIGHT(G4,2)="ir",LEFT(G4,LEN(G4)-2)&"iéremos","~")))

If for some inexplicable reason you are interested you can test it and see what it does by starting an Excel spreadsheet and putting a word whose last two letters are either "ar", "er", or "ir" into cell G4. Paste the above formula into some other cell in the vicinity and watch what happens. Try a word that doesn't end in "ar", "er", or "ir".

I say 9546 "different" formulas, but there are really only 43 formulas that are truly different, the others are all just cell reference changes, i.e., instead of "G4" it might be "B60" or "G1530" etc., and they were generated by cut-and-paste. As you've probably guessed, this spreadsheet generates Spanish verb conjugations. This formula generates (just for you, Bitt! ) the first person plural imperfect subjunctive. I am using the spreadsheet to make up a set of flash cards, and the purpose of the keyboard macro is to change irregular verbs to be bold-14pt-red after I type them in manually. Lots of fun...

tanstaafl.

*Shift-Home
Ctrl-1
Tab
Tab
Down-arrow
Down-arrow
Tab
14
Tab
Tab
Tab
Tab
Tab
Down-arrow
Tab
Down-arrow
Down-arrow
Down-arrow
Enter
Tab
Tab
Enter
Down-arrow

You probably want to look into "conditional formatting".

Add, change, or remove conditional formats
1) Select the cells for which you want to add, change, or remove conditional formatting...

And therein lies the problem. The cells I have to conditionally format are semi-randomly distributed throughout the 34,188 [so far - it will grow as I add more verbs] cells of the spreadsheet. A "formatted" cell is indistinguishable from a "non-formatted" cell until I actually make the change by typing in the irregular verb. At that point, it is easier to just let the "Enter" key do the formatting for me by means of the keyboard macro. All I have to do is remember to suspend the macros when I'm not working on the flash cards.

Hmmm... maybe something along the line of "If the cell does not contain a formula, change it to bold 14pt red", but that would be a problem because there are lots of "non-verb" cells (column headers, etc.) that are not to be re-formatted that also do not contain formulas. No, the way I'm doing it is probably best, at least with my limited knowledge of Excel. If I didn't have the macro keyboard, my attitude would be different, I'm sure!

As long as we're talking about Excel spreadsheets, I have a question. I made the mistake of highlighting a group of columns (not a group of cells, but columns covering rows 1 through infinity) and setting up gridlines. Somehow (I'm not sure how or why, because I can't reproduce this but it has happened to me before) I ended up with my end-of-file row being thousands of rows past the end of my data. (I define end-of-file as being the cell you end up at when you type "Ctrl-End".) The only way I've ever been able to fix this has been to copy my data to the clipboard, open up a new spreadsheet and paste the data. I've never been able to "shorten" an extended spreadsheet back to just the end of the data. How can I do that?

tanstaafl.

I assume you have a set of columns that are the automatically declined verbs, and you're manually going back through to find the cells whose automatic declensions are incorrect due to irregularity and entering the irregularly declined forms manually.

So select those columns, manually deselect the header cells, and apply the conditional format, matching on lack of formula.

That would work, but it is easier said than done, Bitt. There are a LOT of column headers (see attached sample) and in a few cases there are regular verbs without formulas because the verb doesn't end in "ir", it ends in "ír" (with accent mark) which puts the formula into failure mode and writes out "~" instead of the declension. Those verbs have to be manually typed in even though they are (for the most part) regular, and thus they lack formulas.

Once my macro is set up, each irregular verb gets formatted red/bold automatically as I type it.

Now I need to set up another macro to repair some missing grid lines. (880 of them!) To do this, I need to emulate a left mouse click with the keyboard. Anybody got any ideas on how to do that? Oh, wait, it's only 36 grid lines to fix. I'll do that manually. But, is it possible to right-click via keyboard?

(right-click the picture and select "View Image" to see detail.)

tanstaafl.

Normally, there's a right-click key on the right-hand side of the keyboard, just left of the right-hand side Ctrl-key.

Stig

Gaaah! I meant to say is it possible to left-click via the keyboard. Normally Shift-F10 will emulate a right-click. My keyboard is so old (a Gateway 2000 AnyKey dating back to the early 1990s) that it doesn't have any of those fancy-schmancy keys like the new ones. I was running Windows 3.1 when I got this keyboard.

tanstaafl.

That would be the "Enter" key (in most situations)

Unfortunately, not in this situation. I need a left mouse click to select an item in Excel (Format->Cells->Borders->|) and ONLY a left mouse click will select it. Enter key does nothing. In other instances an Enter exits the window instead of selecting an item from a drop-down menu.

I haven't yet found anything that will purely emulate a left mouse click.

tanstaafl.

Doug, I know you have a soft spot for your AnyKey — I had one too, and still do, somewhere — but you're doing it wrong.

You need to separate your data from your layout. You need to put the data in another sheet as simple rows and columns, and then have another sheet that does all the fancy display.

I dunno, Bitt. That would certainly be a more elegant solution and perhaps more technically correct, but it would add several layers of complexity (an extra sheet, display/printout separated from the data, etc.) to what is a relatively simple group of rows and columns. As things are now the only complication is that some of the cells are a different color, and that was dealt with during the data entry. Well, the formulas are a bit tricky I guess... The whole purpose is to print out and cut each page up into four pieces. Precision control of the output is critical and I don't want to dilute the WYSIWYG aspects of the spreadsheet. Probably if I were as well-versed in Excel as I know you are I would see things differently. (All my Excel knowledge is self-taught with a good bit of guidance from Paul Grzelak.)

And yes, I love my AnyKey. I have a spare tucked away in case this one ever dies. I use those keyboard macros a lot and would be lost without them. One macro that I use 40 or more times per week consists of 106 keystrokes!

tanstaafl.

I have to interject that I use both Autohotkey and Strokeit to fill my macro needs. Both are excellent programs, and are easily programmed.

Autohotkey watches for specific keystrokes and then performs a macro, while strokeit utilizes mouse gestures to do the same.

Here at work, I use autohotkey to translate UPC barcodes into the item numbers we use to track inventory (These numbers are human readable, UPC is not.) all I have to do is scan the barcode, AHK sees the matching string, deletes it, and re-enters the corresponding code.

Those are interesting programs, particularly Strokeit, but neither one fits my needs.

Strokeit seems to substitute mouse action for keyboard, instead of the other way around. Very clever idea, but I usually need to do just the opposite.

AutoHotkey is much more powerful and versatile than my AnyKey keyboard, but "easily programmed" is a bit of a misnomer, at least compared to AnyKey.

To create a new script:

1. Download and install AutoHotkey.
2. Right-click an empty spot on your desktop or in a folder of your choice.
3. In the menu that appears, select New -> AutoHotkey Script. (Alternatively, select New -> Text Document.)
4. Type a name for the file, ensuring that it ends in .ahk. For example: Test.ahk
5. Right-click the file and choose Edit Script.
6. On a new blank line, type the following: #space::[your script here]
7. Save and close the file.
8. Double-click the file to launch it. A new icon appears in the taskbar notification area.
9. To exit or edit the script, right-click the green "H" icon in the taskbar notification area.

So, to create and run a macro with AutoHotkey you have to transfer from keyboard to mouse and back a minimum of three times. Just to run an existing macro will require at least one transition. This would not work well for me the way I use macros. For example, to type the Spanish verb for "he is" I type está, that is, e, s, t, and ctrl-a because I have ctrl-a reprogrammed in my keyboard to be "alt-160". With AutoHotkey, I would type e, s, t, and then drag out the mouse, bring up Windows Explorer, find and open the directory with my *.ahk macros, scroll down to the one named "a with accent grave.ahk" (unless I were running Linux, then it would have to be "a_with_accent_grave.ahk" ), double click on the file, then close the file, close the directory, close Windows Explorer, and resume typing. This does not seem optimal to me.

To program my keyboard, I press the "ProgrmMacro" key, press the key the macro will be stored in (ctrl-a in the above example), record my macro, and press the ProgrmMacro key again to stop the recording. After that, any time I press the "stored in" key, the macro runs unless the "Suspend Macro" key is toggled in which case all macros are disabled.

I use this "instant programming" constantly for things like, oh, say I was in an Excel spreadsheet and for some silly reason needed to copy a cell, paste it into a new location two columns over and one row down, make the pasted cell bold with a border around it, and repeat this operation on every fifth row. I just now programmed my AnyKey to do this and it took me 19.7 seconds. I usually "macro-ize" any operation that repeats three or more times and I use the "/" key on the numeric keyboard for these throwaway macros since it is located adjacent to the "ProgrmMacro" key.

On the off chance that anyone is anal enough to want to know what a macro to copy a cell and paste it two columns over and one row down, make it bold with border, and repeat looks like...

Ctrl-PrgrmMacro

/
Ctrl-C
Tab
Tab
Down-Arrow
Ctrl-V
Ctrl-B
Ctrl-1
B
Tab
Ctrl-O
Enter
Home
Enter
Enter
Enter
Enter

ProgrmMacro

will do the trick. Note that all of this, programming and running the macro, was done without touching the mouse.

I'm kinda weird, I know, but what can I say? It works for me.

tanstaafl.

Or you could just use some software to create Compose keys or Dead keys on your keyboard, and that wouldn't prevent you from using Ctrl-A.

As for the more complex things, there are certainly also pieces of software that do macros via hotkey, AutoHotkey being one of them. (You didn't investigate it very thoroughly.) They also often have built-in functions for doing mouse actions, including AutoHotkey.

FWIW, even if you could generate a left click with your keyboard, it would click wherever your mouse happened to be, not on the item you intended to click on. And you can do mouse clicks with your keyboard. You just need to turn on MouseKeys.

The advantage to all of this is that you don't have to reprogram your keyboard and get yourself stuck when trying to log in.

Ah, yes, I may have oversimplified AHK, there IS a slight learning curve. What I DO like about it is the fact that we have over 9000 different macros and they all work flawlessly.

9000 hot keys!

How do you keep track.

I don't. I wrote an excel spreadsheet to build the script. Column A has the UPC Codes. Column B has our codes. Column C builds the scripts. If we scan a barcode and it doesn't replace it with our code, we simply add it and refresh the script. Easy peasy.