CODEWITHSUNDEEP
excelvstovalidation
user3111311
user3111311

Reputation: 8011

Data validation for time in format 00:00:00 in Excel VSTO?

Data validation for time in format 00:00:00 in Excel VSTO?

I can do data validation for time in Excel cell, but the strange thing is it also allows you to enter integers and doubles in the cell. Instead I want to allow ONLY values in the format 00:00:00. I see you can make a custom formula for data validation in excel, but is there a formula that can check this?

Upvotes: 0

Views: 1090

Answers (1)

Bernardo Dal Corno
Bernardo Dal Corno

Reputation: 2088

Yes. While I've never used VSTO myself, you can use this formula:

=IF(TIMEVALUE(TEXT(A1,"hh:mm:ss"))=A1, "Time Entry", "Not a Time Entry")

Programatically (VBA) there's certainly a lot of ways to accomplish the same results, and it depends on your context. You could for instance listen to the event Worksheet_Change and call this code in the worksheet module:

Function IsTime(rng As Range) As Boolean
    Dim sValue As String
    sValue = rng.Cells(1).Text
    On Error Resume Next
    IsTime = IsDate(TimeValue(sValue))
    On Error GoTo 0
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
  If not(IsTime(Target.Value)) then
    msgbox "Please enter time in '00:00:00' format!"
    Target.Value = ""
  End if
End Sub

Source: http://excel.tips.net/T003292_Checking_for_Time_Input.html

Upvotes: 0

Related Questions