PDA

View Full Version : Restrict Cell entry to minutes and seconds only



khalid79m
03-02-2009, 03:21 AM
How can I restrict cell entry to

minutes and seconds

or

hour minutes and seconds

using : seperator ??

any help??:help

Simon Lloyd
03-02-2009, 04:17 AM
You could use Data VAlidation and set the cell as Time!

khalid79m
03-02-2009, 04:22 AM
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
03-02-2009, 04:27 AM
What do you mean by it always turns to 1, it works for me, including making the cells green.

khalid79m
03-02-2009, 04:53 AM
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
03-02-2009, 04:56 AM
That time works fine for me.

khalid79m
03-02-2009, 05:24 AM
why doesnt it work for me?

mdmackillop
03-02-2009, 11:51 AM
I couldn't get it to fail either.

Simon Lloyd
03-02-2009, 11:53 AM
Hmmmm, do you have custom formatting set up for the cell?

khalid79m
03-12-2009, 07:53 AM
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