Advanced Excel---Excel Tricks and Tips PART II

Canceling a Command Or Macro Run

If 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

If you begin to select an option from one of the Excel menus, you can cancel the menu by one of three methods:

• 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

Milan wonders how he can configure Excel so that when he enters a single digit it will automatically advance to the next cell. He wants to eliminate hitting Enter or Tab to get to the next cell. The value of the entry for a range of cells will always be a single positive 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

Want a neat trick you can use to make your worksheets more helpful to users? How about adding a pop-up window that displays documentation whenever the cell is selected? To add such a feature, follow these steps:

1. Select the cell for which you want the pop-up to appear.
2. Display the Data tab of the ribbon.
3. In the Data Tools group, click the Data Validation tool. Excel displays the Data Validation dialog box.
4. Make sure the Input Message tab is displayed. (See Figure 1.)

Figure 1. The Input Message tab of the Data Validation dialog box.
5. Make sure the Show Input Message When Cell is Selected check box is selected.
6. 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.)
7. In the Input Message box, enter the text of the documentation you want to appear in the pop-up window.
8. Click OK.

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....