formatting question

[anny]

hi

I have a scoring program in which the users at an athletic competition have
to enter hundreds of scores, sometimes fairly quickly. The scores are
entered in various worksheets, but always in the same range, "M8:M37".
These cells are formatted for numerical values with 3 decimals and are
between 0.000 and 9.999.

Question 1: To speed up data entry, how can I code this so the decimal
doesn't need to be entered?

9 should show 9.000 as it currently does, but 915 should show 9.150, etc.
(decimal after the first digit)

Question 2: Actually scores of 10 are possible, though extremely rare. I
would need some kind of override if it ever came up so that the 10 doesn't
show as 1.000 Can this be easily done?

thank you
anny

 

[Peter T]

In Tools/Options/Edit tab look at Fixed Decimal, tick & change to 3.

Your users would need to be educated that for 9.15 they can either enter
9150 or 9.15 with the point is also OK, but 915 is not.

Various ways to cater for numbers entered outside min/max predicated times,
eg

WorkSheet_Change event
Data Validation
Conditional formatting

Regards,
Peter T

 

[Ken Johnson]

Hi anny,

You could try this Worksheet_Change Event Sub...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ERRORHANDLER
If Target.Column = 13 _
And Target.Row > 7 _
And Target.Row < 38 _
And Target.Rows.Count = 1 _
And WorksheetFunction.IsNumber(Target.Value) _
And Target.Value <> 10 _
And Mid(Target.Value, 2, 1) <> "." Then
Application.EnableEvents = False
Dim strX() As String
Dim sShownValue As Single
Dim I As Byte
ReDim Preserve strX(1 To Len(Target.Value))
For I = 1 To UBound(strX)
strX(I) = Mid(CStr(Target.Value), I, 1)
Next
Select Case UBound(strX)
Case 1
sShownValue = CSng(strX(1) & "." & "000")
Case 2
sShownValue = CSng(strX(1) & "." & strX(2) & "00")
Case 3
sShownValue = CSng(strX(1) & "." & strX(2) & strX(3) & "0")
Case 4
sShownValue = CSng(strX(1) & "." & strX(2) & strX(3) & strX(4))
End Select
Target.Value = WorksheetFunction.Round(sShownValue, 3)
End If
If Target.Cells(1, 1).Value = 0 Then
Application.EnableEvents = False
Target.Cells(1, 1).ClearContents
Application.EnableEvents = True
Beep
Target.Select
End If
Application.EnableEvents = True
Exit Sub
ERRORHANDLER: Application.EnableEvents = True
End Sub


If the user enters a value with more than 4 digits the code assumes a
mistake has been made. The entry is cleared, the cell is reselected and
a beep is sounded.
If the user uses the decimal point, the code is skipped and the entry
is as usual.
If 10 is entered the code is skipped and it apears as 10.000.

Ken Johnson

 

[Ken Johnson]

Hi anny,

I forgot...To get the code in place...

1. Copy it
2. Right click the sheet's sheet tab then select "View Code" from the
popup.
3. Paste the code.
4. Alt + F11 to return to the worksheet

Ken Johnson

 

[anny]

looks excellent, thanks so much
anny

 

[Ken Johnson]

You're welcome anny.
Thanks for the feedback.
Ken Johnson

 

[Ken Johnson]

Hi anny,

I've noticed that clearing the contents of any cell outside the range
M8:M37 results in the beep and the cleared cell being reselected (if
Enter is set to move down).
This can be cured by moving the first instance of "End If" down 6 lines
so that it is either the line before or the line after after the second
instance of "End If".

Ken Johnson