Did you ever stop to think of what goes on behind the scenes in Excel when you enter something into a cell? I came up with a rough list:
First keystroke:
- If the cell locked and the sheet is protected, display error
- Update the formula bar and/or the cell
- If it’s an equal sign, at symbol, minus sign, or plus sign change status bar and Name box
- Disable inappropriate menu items
- Suggest Autocomplete, if possible and enabled
Other keystrokes:
- Update the formula bar and the cell display
- Monitor keyboard for things such as Ctrl+B and Ctrl+I
- Monitor keyboard for things such Shift+F3 to insert a function
- Monitor toolbars for formatting changes
- Suggest Autocomplete, if possible and enabled
Enter key (or arrow key):
- Check for valid formula and display error or pop-up
- Check for names used
- Check for external references, and retrieve values if necessary
- Check for dependent formulas and recalc if necessary
- Check the 1-2-3 formula evaluation setting
- Check for data validation
- Check of conditional formatting
- Check the AutoCorrect list and adjust if necessary
- Check if the cell has a style, and adjust appropriately
- Check to see if it’s part of an array
- If it’s a number, display it using appropriate number format
- Display it using correct alignment
- Display it using correct font, size and attributes
- Adjust row height, if necessary
- Autoadjust column width if appropriate
- Update formula bar
- Check for a VBA event-procedure (e.g., Worksheet_Change)
- Check if it’s a hyperlink
- Activate next cell if appropriate
- Display formula error SmartTag if necessary
- Check to see if the cell is in the Watch list
- Check to see if cell is contained within a camera tool picture
- Update charts, if necessary
- If text-to-speech is active, speak the cell contents
What have I missed?
Always monitor the keboard for the F1 Help key
On random arrow key presses shut excel and display BSOD
Hui…
If rand()
If the keystroke is the last character in a cell address, Excel colors the text in the address bar and adds a colored outline to the referenced cell on the worksheet.
John,
You missed
Disable Macros – You cant run macros in the cell edit mode
Regards
Sam
Related to “display error or pop-up” for Enter key, show circular error and reference in status bar if necessary.
Color code the parentheses
In the other keystrokes section, color code the parentheses.
With respect to:
Check for dependent formulas and recalc if necessary
The cell needs to be tagged as dirty (if appropriate) to be recalced
You missed … if I can’t work out what the h*ll’s going on, go to the error routines and display something beginning with #