Solved: Minutes and Seconds Validation

[khalid79m]

'Unique TT NamedRanges
With Range("TT")
.Locked = False
.Validation.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="00:01", Formula2:="99:99"
.Validation.IgnoreBlank = False
.Validation.ErrorTitle = "Time Error"
.Validation.ErrorMessage = "Please Enter Minutes & Seconds in this format 23:59"
.Validation.ShowError = True
End With

What I want to achieve is with range "TT" I want the user to be only able to put in minutes and seconds in the format 23:59 (twenty three minutes and 59 seconds) ... i cant get it to do that , the nearest ive got is with a decimal point rather than : , but the problem with decimal it accepts whole numbers :) so someone could potentionally type in 23 and it would accept it and display it as 23 rather than 23:00 (23 minutes 0 seconds)

can and one help ....????

[Bob Phillips]

Dim cell As String
'Unique TT NamedRanges
With Range("TT")
cell = .Cells(1, 1).Address(False, False)
.Locked = False
.Validation.Delete
.Validation.Add Type:=xlValidateCustom, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlGreater, _
Formula1:="=AND(" & cell & ">=TIME(0,0,1)," & cell & "<=TIME(23,59,59))"
.Validation.IgnoreBlank = False
.Validation.ErrorTitle = "Time Error"
.Validation.ErrorMessage = "Please Enter Minutes & Seconds in this format 23:59"
.Validation.ShowError = True
End With

[khalid79m]

Could you explain the code, Why dim cell as string ??

[Bob Phillips]

Because I am getting the address of the first cell in the range TT, and that is a string.

[khalid79m]

Is there anywhere I can learn more vba script writing?

[georgiboy]

This very forum is a wealth of info.. you can look through the knowledge base, you can look through articles, you can look at other peoples problems and solutions to try and understand how they were solved. Basicly there is alot of readind to do, i have gained alot from this forum and its free.

[khalid79m]

Thanks mate, I will search the knowledge base more before I post