Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If statement question
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) "Patrick Simonds" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF - And - Then Statement question | Excel Worksheet Functions | |||
IF Statement question | Excel Discussion (Misc queries) | |||
IF statement question | Excel Worksheet Functions | |||
IF Statement Question | Excel Worksheet Functions | |||
IF Statement question | Excel Worksheet Functions |