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.

Garth

ToolsOptionsEdit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP


On Mon, 20 Nov 2006 18:28:02 -0800, Garth Hales
wrote:

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;

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" wrote:

Garth

ToolsOptionsEdit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP


On Mon, 20 Nov 2006 18:28:02 -0800, Garth Hales
wrote:

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.

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.

In article ,
Garth Hales wrote:

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" wrote:

Garth

ToolsOptionsEdit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP


On Mon, 20 Nov 2006 18:28:02 -0800, Garth Hales
wrote:

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.

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

"JE McGimpsey" wrote in message
:

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.

In article ,
Garth Hales wrote:

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" wrote:

Garth

ToolsOptionsEdit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP


On Mon, 20 Nov 2006 18:28:02 -0800, Garth Hales
wrote:

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.

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

On Mon, 27 Nov 2006 09:20:02 -0800, Garth Hales
wrote:

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" wrote:

Garth

ToolsOptionsEdit.

Check "Fixed Decimal Places" and set for 2

Enter 1234 returns 12.34


Gord Dibben MS Excel MVP


On Mon, 20 Nov 2006 18:28:02 -0800, Garth Hales
wrote:

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 MS Excel MVP

One reference you should always check:

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

On Error GoTo 0 disables error handling in the current procedure. It
doesn't specify line 0 as the start of the error-handling code, even
if the procedure contains a line numbered 0. Without an On Error GoTo
0 statement, an error handler is automatically disabled when a
procedure is exited.


In article , "JMay" wrote:

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?

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" wrote in message
:

One reference you should always check:

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

On Error GoTo 0 disables error handling in the current procedure. It
doesn't specify line 0 as the start of the error-handling code, even
if the procedure contains a line numbered 0. Without an On Error GoTo
0 statement, an error handler is automatically disabled when a
procedure is exited.


In article , "JMay" wrote:

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?

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.


In article , "JMay" wrote:

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 JE for the in-depth response; much appreciated.
Jim

"JE McGimpsey" wrote in message
:

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.


In article , "JMay" wrote:

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?