VBA: On worksheet change, convert number into time

[FryGirl]

Hello all,

I have this working on column F to convert the value entered into time. So, for example, if the user enters 210, it's converting it to 3:30.

This is working good, but two questions if I could.

1) Is this the most efficient way to do this?
2) What can I do to validate that a number was entered?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range: Set rng = Target.Parent.Range("F:F")
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row < 2 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    Dim x As Variant: x = Target.Value
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Undo
    End With
    
    Target.Value = x / 1440
    Target.NumberFormat = "[h]:mm"
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

 

[Rick Rothstein]

What is the purpose of Application.Undo at the start of your code?

 

[FryGirl]

I'm not 100% sure. I was crafting this off of another thread, so may have not realized I didn't need it.

I thought I needed it to capture the user's input so I could then do the conversion afterwards.

 

[FryGirl]

Bump

 

[Alex Blakenburg]

I can't advise on whether it is the most efficient way but in terms of whether it is a number the following will give you a true if it is a number.

IsNumeric(Target)