Canceling a Command Or Macro RunIf you start to perform a command or action within Excel, you may want to cancel it before it finishes. To do this, the general rule is to simply press the Esc key. This should cancel any command or action that Excel is processing. It is also interesting to note that you can press Esc to close most every dialog box that may pop up in Excel.
Canceling a Menu
- Press Esc or Alt twice (the second time is necessary to return you to your worksheet)
- Click on the menu name a second time
- Click somewhere outside the menu area
Accepting Only a Single Digit
This cannot be done with any native configuration setting in Excel. Instead, you will need to create a macro that will handle the entry for you. A natural choice for the macro is to use the Change event for the worksheet, so that any time a value is entered into a cell, the entry is "pulled apart" and stuffed in cells in the row.
Private Sub Worksheet_Change(ByVal Target As Range) If IsNumeric(Target.Value) Then CRow = Target.Row CColumn = Target.Column - 1 Entry = Target.Value For i = 1 To Len(Entry) Cells(CRow, CColumn + i).Value = Mid(Entry, i, 1) Next End If End Sub
This macro checks, first, to see if what was entered is numeric. If it is, then the digits are extracted from the value and placed in consecutive cells in the row.
The drawback to such a macro, of course, is that you still need to press Enter to trigger the event. If you want to get away from pressing Enter entirely, then you will need to rely upon a different approach. This technique relies upon the OnKey function to assign macros to specific keystrokes. Place the following code into a standard macro module.
Sub Assigns() Dim i As Variant With Application For i = 0 To 9 .OnKey i, "dig" & i Next End With End Sub
Sub ClearAssigns() Dim i As Variant With Application For i = 0 To 9 .OnKey i Next End With End Sub
Sub dig0() ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select End Sub
Sub dig1() ActiveCell.Value = 1 ActiveCell.Offset(1, 0).Select End Sub
Sub dig2() ActiveCell.Value = 2 ActiveCell.Offset(1, 0).Select End Sub
Sub dig3() ActiveCell.Value = 3 ActiveCell.Offset(1, 0).Select End Sub
Sub dig4() ActiveCell.Value = 4 ActiveCell.Offset(1, 0).Select End Sub
Sub dig5() ActiveCell.Value = 5 ActiveCell.Offset(1, 0).Select End Sub
Sub dig6() ActiveCell.Value = 6 ActiveCell.Offset(1, 0).Select End Sub
Sub dig7() ActiveCell.Value = 7 ActiveCell.Offset(1, 0).Select End Sub
Sub dig8() ActiveCell.Value = 8 ActiveCell.Offset(1, 0).Select End Sub
Sub dig9() ActiveCell.Value = 9 ActiveCell.Offset(1, 0).Select End Sub
To start the macro, run the Assigns macro. Thereafter, every time a digit is typed the digit is stuffed into the current cell and the next cell to the right selected. If you type in text, then nothing happens. (Of course, if you try to enter a mixed value, such as B2B, then when you press "2" that is what will end up in the cell.) When you are done with this type of data entry, run the ClearAssigns macro to finish up.
Adding Pop-Up Documentation to a Cell
- Select the cell for which you want the pop-up to appear.
- Display the Data tab of the ribbon.
- In the Data Tools group, click the Data Validation tool. Excel displays the Data Validation dialog box.
- Make sure the Input Message tab is displayed. (See Figure 1.)
- Make sure the Show Input Message When Cell is Selected check box is selected.
- In the Title box, enter a title for the pop-up window. (This title appears in bold at the top of the pop-up window.)
- In the Input Message box, enter the text of the documentation you want to appear in the pop-up window.
- Click OK.
Figure 1. The Input Message tab of the Data Validation dialog box.
There are, obviously, other ways you can use the Data Validation feature of Excel to check and limit the data that is entered in a cell. However, if you follow the steps above, all data is still accepted, and Excel displays a helpful message whenever the cell is selected.
Will continue the tricks and tips in Next PART!!! Have a great time....