If statement question

[Patrick Simonds]

The code below is designed to change a number input, such as 1425 to 14:25.
Is there anything I can do prevent the code from running if the number is
already properly inputted?


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim e

ActiveSheet.Unprotect

On Error GoTo ErrorHandler

If Not Application.Intersect(Target, Range("E3:E8000")) Is Nothing Then

If ActiveCell < "" Then GoTo ErrorHandler

e = Left(Format(Target.Value, "0000"), 4)
Application.EnableEvents = False
Target.Formula = Left(e, 2) & ":" & Right(e, 2)

End If


Application.EnableEvents = True

If ActiveCell = ":" Then GoTo ClearCell

Exit Sub

ClearCell:

Selection.ClearContents

Exit Sub

ErrorHandler:


Exit Sub:

End Sub

 

[MrScience]

Would it help to use the InStr function? This is what I usually do if
I understand your question correctly. You could use something like . .
..

Dim lkFor as Integer
set nextCell = myCell.Offset(1,0)
lkFor = InStr(myCell, ":")
If lkFor > 0 then
' now you know the colon character exists and handle accordingly
set myCell = nextCell
End If

 

[Bob Phillips]

Test it for numeric

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim e

Application.EnableEvents = False
Sh.Unprotect
On Error GoTo ErrorHandler

If Not Application.Intersect(Target, Range("E3:E8000")) Is Nothing Then

If Not IsNumeric(Target.Value) Then GoTo ErrorHandler

e = Left(Format(Target.Value, "0000"), 4)
Target.Formula = Left(e, 2) & ":" & Right(e, 2)

ElseIf Target.Value = ":" Then GoTo ClearCell

Exit Sub

ClearCell:
Target.ClearContents
Exit Sub

ErrorHandler:

Exit Sub:
Application.EnableEvents = True

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)