The reason your values were going to 0 is that each time you wrote the value
back to the cell, the event was called.
Bob stopped that with the "application.enableevents = false" line.
He wrote to the cell, and then reenabled events for the next time.
Bob's code will convert 2315 to 23:09:00 (hh:mm:ss).
I thought that you would want to enter 2315 to get 23:15:00 (hh:mm:ss).
And I had concerns why this is in a workbook_sheetchange event.
In most cases I've seen, users want to limit this kind of change to a single
worksheet (and maybe a single range).
If that seems reasonable, this code would go in that worksheet's module.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
If IsNumeric(Target.Value) Then
Select Case Target.Value
Case Is < 1: 'do nothing
Case Is 2359: 'do nothing
Case Else
Application.EnableEvents = False
Target.Value = Format(Target.Value, "00:00") & ":00"
Target.NumberFormat = "hh:mm"
End Select
End If
errHandler:
Application.EnableEvents = True
End Sub
(I limited my range to column A. Change that to what you want--along with the
numberformat, too.)
"MelissaCreek <" wrote:
Hi,
What I am trying to achieve is to be able to alter the contents of a
particular cell. If I type 2300 into the cell I want the cell to
automatically update with 23:00
The code I have so far is:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim TargetRow As String
Dim TargetColumn As String
Dim IntValue As String
Dim TargetHours As String
Dim TargetMins As String
TargetRow = Target.Row
TargetColumn = Target.Column
IntValue = Format(Target.Worksheet.Cells(Target.Row,
Target.Column).Value, "0000")
TargetHours = Left(IntValue, 2)
TargetMins = Right(IntValue, 2)
Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" &
TargetMins
End Sub
the trouble is that the cell gets updated with 00:00 all the time.
When I try to format the cell (after it has been formatted) it is
always on a custom format of hh:mm which is OK but I am thinking the
formatting may be at fault somewhere??
I am using office 2000.
Any help is appreciated.
---
Message posted from http://www.ExcelForum.com/
--
Dave Peterson