Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to emulate a ten-key entry in Excel?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel file with hyperlinks takes a long time to open over the network | Links and Linking in Excel | |||
Excel formula timestamp for a data entry of a referenced cell | Excel Worksheet Functions | |||
display last entry in Excel | Charts and Charting in Excel | |||
How do i make journal entry in excel? | Excel Worksheet Functions |