Restrict Cell entry to minutes and seconds only

[khalid79m]

How can I restrict cell entry to

minutes and seconds

or

hour minutes and seconds

using : seperator ??

any help??

[Simon Lloyd]

You could use Data VAlidation and set the cell as Time!

[khalid79m]

 With Range("Talk_Time")
                    cell = .Cells(1, 1).Address(False, False)
                    .Locked = False
                    .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
                    .WrapText = False
                    .ColumnWidth = 7
                    .Interior.ColorIndex = 43
                    .EntireColumn.Hidden = False
                End With

I currently use this code but it always turns the time to 1??

[Bob Phillips]

What do you mean by it always turns to 1, it works for me, including making the cells green.

[khalid79m]

It works for me sometimes, but more often than not it shows a value of 1

for example talk time was 1hr 20munites and 9 seconds.

So I input 1:20:09
It displays 0
in the formula bar it show 0.0556597222222222

im confused.

[Bob Phillips]

That time works fine for me.

[khalid79m]

why doesnt it work for me?

[mdmackillop]

I couldn't get it to fail either.

[Simon Lloyd]

Hmmmm, do you have custom formatting set up for the cell?

[khalid79m]

Not as far as I am aware, but there maybe some other script within another module that might be chaning the formatting. I will check and get back