Document Specific Autocorrection

[TheBroon]

I'm trying to create an excel spreadsheet that allows the user to input a
number of hours worked for any given date, but for the spreadsheet to add
only the previous 28 day's worth of hours. Everything is going fine, except I
want to be able to input 1 hour 35 minutes as 1.35 and not 1:35. I found
autocorrection could automatically change the "." to a ":" so it's still
understood as a time by excel. However, adding this to autocorrection changes
my default settings across the program. Is there a way to make this file
specific, so that it affects only this file, and so that it will carry over
if another user opens the file on their computer.

Equally, if autocorrection cannot do this, is there a cell specific formula
to do this, or any other way to input the time using a full stop instead of a
colon?

Thanks,

Chris

 

[Mike H]

Hi,

Autocorrect is global so that solution would change all . to : in Word which
would be a bit of a pain. Try this instead. Right click your sheet tab, view
code and paste this in on the right. It's cirrently set to work on A1:a100

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Value = WorksheetFunction.Substitute(Target.Value, ".", ":")
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike

 

[TheBroon]

Just the job - thanks Mike!

Hi,

Autocorrect is global so that solution would change all . to : in Word which
would be a bit of a pain. Try this instead. Right click your sheet tab, view
code and paste this in on the right. It's cirrently set to work on A1:a100

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target.Value = WorksheetFunction.Substitute(Target.Value, ".", ":")
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike

 

[TheBroon]

Actually, it turns out that didn't quite do it...had a few flaws, like "1.00"
became ":10" instead of "1:00". and "1.10" became "1:01" instead of "1:10"!

Talking to a friend, he finally sorted it out. So for anyone else trying the
same thing, the code he put together was:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("E1:E100")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False

Dim str As String
Dim lft As String
Dim regs As String
Dim pos As Integer
Dim res As String

str = WorksheetFunction.Substitute(Target.Value, ".", ":")
pos = InStr(1, str, ":", vbTextCompare)

If (pos > 0) Then
lft = Left(str, pos - 1)
regs = Right(str, Len(str) - pos)
If Len(regs) = 1 Then regs = regs + "0"
res = lft + ":" + regs
Else
res = str + ":00"
End If

Target.Value = res


Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub