Is it possible to emulate a ten-key entry in Excel?

[Guest]

I want to set up a quick entry system in one column of a spread sheet. It
should act like the old ten-key calculator or adding machine set for
accounting style entry - that is all entries are assumed to end in two
decimal places, eliminating the typing of the decimal point itself.

 

[Gord Dibben]

Garth

Tools>Options>Edit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP

 

[Guest]

Gord;

This works but makes the change apply to all spreadsheets. I can live with
that but would really like to have the emulation apply only to the current
spreadsheet or ideally to only a column. Any ideas?

Thanks again,

Garth Hales

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nCOL As Long = 4 'e.g., 4 = column D
With Target
If .Cells.Count > 1 Then Exit Sub
If .Column = nCOL Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = .Value / 100
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End With
End Sub

Change the 1st line's column number, nCOL, to suit.

 

[JMay]

JE -- The reference books I have are not clear on the meaning or use
of
On Error GoTo 0 -- can you clarify what it does?
Jim

 

[Gord Dibben]

Garth

Try this event code which divides any number entered in Column B by 100

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Cells.Column = 2 Then
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value / 100

End With
End If
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste into that module.

Adjust to suit......Column = 2 is B = 3 is C, etc.


Gord

Gord;

This works but makes the change apply to all spreadsheets. I can live with
that but would really like to have the emulation apply only to the current
spreadsheet or ideally to only a column. Any ideas?

Thanks again,

Garth Hales

Gord Dibben MS Excel MVP

 

[JE McGimpsey]

One reference you should always check:

From XL/VBA Help ("On Error Statement"):

 

[JMay]

Thanks JE,

So when I see:

On Error GoTo 0
End If
End If
End With
End Sub

Isn't it (the On Error GoTo 0 line) sort of inconsequential
To disable error checking when there is only
End If
End If
End With
End Sub

AND also because, with the End Sub - the default error Checking is
"turned-back-on" for the next procedure?

Thanks,
Jim

 

[JE McGimpsey]

Yes, On Error GoTo 0 is strictly unnecessary in this case.

I usually include it in my posts because I assume that code may be added
to the snippets I provide. If the coder doesn't recognize the change in
error routines when they add that code, they may miss run-time errors.

In my own programming, both personal and commercial, I rarely rely on
the default behaviors. Partly because I won't necessarily remember the
code logic after six months (and another programmer wouldn't stand a
chance), though I tend to document pretty thoroughly. It's more of a
programming philosophy than anything else - if one sets a property, one
explicitly resets it on exit. It's a kind of discipline that has paid
dividends for me over the years.

The main exception I make is destroying objects. My experience, and a
bit of corroboration from others, leads me to believe that VBA's garbage
collection and end of procedure routines handle destroying out-of-scope
objects more efficiently than explicitly destroying them in code.

 

[JMay]

Thanks JE for the in-depth response; much appreciated.
Jim