Formatting To Time

[Johnboy]

Hi All,

To format text in cell with macro.

Macro has input box and ask user to input time without colons...

Range("D7").Select
ActiveCell.FormulaR1C1 = InputBox("Enter Start Time Without Colons HHMMSS ie 151725", "Time")

In cell E7 I have the following formula to add colons so excel recognizes as time.

TEXT(D7,"00\:00\:00")

What I would like is to eliminate cell E7 and have the macro format to time with colons in D7.

 

[Trevor G]

This should give you the format

Sub Macro1()
'
' Macro1 Macro
'

'
Range("D7").NumberFormat = "h:mm:ss"
End Sub

 

[shg]

One way:

    Dim s As String
    
    s = InputBox("Enter Start Time Without Colons HHMMSS ie 151725", "Time")
    If Not s Like "######" Then
        MsgBox "Invalid entry"
        Exit Sub
    End If
    Range("D7").Value = TimeValue(Format(s, "00:00:00"))

 

[Johnboy]

Trevor G - your solution did format the cell but not the text entered.

Shg - Works great... is there a way to loop back to input if invalid entry instead of exiting


Thanks
Johnboy

 

[shg]

    Do
        s = InputBox("Enter Start Time Without Colons HHMMSS ie 151725", "Time")
        If s Like "######" Then Exit Do
        MsgBox "Invalid entry"
    Loop

There would need to be more checking to ensure valid times.

 

[Johnboy]

shg - Thanks for the help... this works great. I understand the checking is not set for time, but this works for now... Again Thanks.

Johnboy

 

[shg]

You're welcome, glad it helped.