loop while not entering a valid time in a cell

[robertvdb]

The user needs to enter a valid timevalue in cell A3. If he slips the finger (invalid time), or if he enters nothing, then I need a loop which either forces him to enter it again, or to leave the current value 09:05.

Thanks in advance.

 

[Georgiboy]

Have you looked at 'Data Validation'?

 

[robertvdb]

Data Validation would be an alternative solution, but I really need a more user-friendly VBA Loop.

 

[bebo021999]

(Right click on tab' name, viewCode, then paste code into)
Not clear what you are trying to do, but here the below code does:
- Input value in cell A3
- As soon as select other cell (or enter), if A3 is invalid time value, a message box pop up with yes/No button:
* If yes: move back to A3 and clear the cell for new input
* If no: add current time in to A3.
If none of above works, try to describe what you want to do, step by step.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim timeV As String, info As String
If Target.Address(0, 0) <> "A3" Then
    On Error Resume Next
    timeV = Format(Range("A3").Value, "hh:mm")
    On Error GoTo 0
    If Len(timeV) <> 5 Then
        With Range("A3")
            info = MsgBox(" Invalid time value! Do you want to input again?" & vbLf & vbLf _
                & "Yes (Input again)" & vbLf & vbLf & "No (leave it as current time", vbYesNo)
            Select Case info
                Case vbYes
                    .Select
                    .ClearContents
                Case vbNo
                    .Value = TimeValue(Now)
            End Select
        End With
    End If
End If
End Sub

 

[robertvdb]

Thanks Bebo. Whereas your solution is valuable, I have been working on my own a bit, and this is what I made:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim timeV As String
Dim answer As String
Dim kol_A_rng As Range

Set kol_A_rng = Range("A3:A3")

If Not Application.Intersect(Target, kol_A_rng) Is Nothing Then
    Do
        timeV = Format(Target.Value, "hh:mm")
        If IsDate(timeV) Then
            timeV = Format(timeV, "hh:mm")
            'MsgBox ("ok")
        ElseIf timeV = "" Then
            Target.Select
            answer = MsgBox("INVALID ENTRY.  TRY AGAIN ?", vbYesNo)
            Application.Undo
            Exit Sub
        Else
            Target.Select
            answer = MsgBox("INVALID ENTRY.  TRY AGAIN ?", vbYesNo)
            Application.Undo
            Exit Sub
        End If
    Loop While Not (IsDate(timeV))
Else
End If
End Sub