Excel VBA- Convert number into time

By Ajeet Kumar

EXCEL VBA: THE NUMBER CONVERTED INTO TIME IN EXCEL SHEET

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngData As Range
    Application.EnableEvents = False
    
    On Error GoTo Errlabel
        
        If IsNumeric(Target.Value) Then
            If Target.Value >= 0 And Target.Value < 24 Then
                Target = Format(TimeValue(WorksheetFunction.Substitute(Format(Target, "00.00;;00.00"), ".", ":")), "h:mm AM/PM")
            Else
                Target.Select
                MsgBox "The value " & Target & " entered is not valid time.", vbInformation + vbOKOnly
                Target.NumberFormat = "General"
            End If
        End If

Errlabel:
    If Err.Number = 13 Then
        Target.Select
        MsgBox "The value " & Target & " entered is not valid time. Please enter correct date.", vbInformation + vbOKOnly
    ElseIf Err.Number <> 0 Then
        MsgBox "Error Number: " & Err & vbCrLf & "Error Description: " & Err.Description
    End If
    Application.EnableEvents = True
End Sub