How can I avoid typing the (:) when typing in the time

[sonar]

Hi

How can I format a field so that a person does not physically have t
type in the ) in for instance the 09:00 etc, but after typing it ge
excel to throw it in automatically? and so not to create an error

 

[JE McGimpsey]

Can't do it via formatting, but you can use VBA:

http://cpearson.com/excel/DateTimeEntry.htm

 

[Don Guillett]

Here is one that works for range a1:a100. Right click sheet tab>view
code>copy/paste this>then just type in number.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Ron Rosenfeld
<[email protected]>
Dim InputRange As Range
Dim Temp As Variant
Dim i As Integer
Dim Separators(2) As String

Separators(0) = ";"
Separators(1) = ","
Separators(2) = "."


Application.EnableEvents = False

Set InputRange = [A1:A100] 'or whatever
If Intersect(Target, InputRange) Is Nothing Then GoTo Bye
If Target.Value = 0 Then GoTo Bye
Temp = Target.Value

'If entry is an integer, then make it a time

On Error GoTo ConvertString
If Int(Temp) = Temp Then
Temp = (Int(Temp / 100) + (Temp / 100 - Int(Temp / 100)) * 100 / 60) /
24
GoTo Last
ElseIf Temp < 1 Then 'probably a time
GoTo Last
End If

If Temp > 15000 Then 'probably a date/time string
Temp = CDate(Temp - Int(Temp))
GoTo Last
End If

ConvertString: 'replace separator with colon unless the string is a
date
On Error GoTo 0
For i = 0 To 2
Temp = Replace(Temp, Separators(i), ":")
Next i


Last:
Target.Value = Temp
Target.NumberFormat = "hh:mm"
Bye: Application.EnableEvents = True
End Sub

 

[sonar]

I have tried it, but it freaked out, could you please have a look at m
file for me and let me know what you would recommend?

The areas they will use to type in hours are as follows:

I14 - I28
I32 - I46
J14 - J28
J32 - J46

T14 - T28
T32 - T46
U14 - U14
U32 - U46

AE14 - AE28
AE32 - AE46
AF14 - AF28
AF32 - AF46

AP14 - AP28
AP32 - AP46
AQ14 - AQ28
AQ32 - AQ46

BA14 - BA28
BA32 - BA46
BB14 - BB28
BB32 - BB46

BL14 - BL28
BL32 - BL46
BX14 - BX28
BX32 - BX46

Unfortunately, I have to make it easier for people who dont know how t
type in hours, I have found that they put in the ( ' ) before th
hours which is not correct, it will throw out the calculations.

Please help.

Regards
An

Attachment filename: mastertimesheet6c.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=53164

 

[Don Guillett]

I opened your workbook and took a look at it. After un-protecting the
worksheet, it worked just fine. In your protection, you must allow
formatting (you didn't mention xl version_______) or leave the sheet
unprotected. Works!

Set InputRange = Range("i14:i28,i32:i46") ' extend to whatever desired using
this idea.

Target.NumberFormat = "hh:mm" 'formatting must be allowed in your
protection.

 

[sonar]

Hi

what do you mean
Target.NumberFormat = "hh:mm" 'formatting must be allowed in your
protection. ?

Also, how can I shorten the following, as it is too long for excel t
accept -

Set InputRange
[I14:I28,I32:I46,J14:J28,J32:J46,T14:T28,T32:T46,U14:U28,U32:U46,AE14:AE28,AE32:AE46,AF14:AF28,AF32:AF46,AP14:AP28,AP32:AP46,AQ14:AQ28,AQ32:AQ46,BA14:BA28,BA32:BA46,BB14:BB28,BB32:BB46,BL14:BL28,BL32:BL46,BM14:BM28,BM32:BM46,BW14:BW28,BW32:BW46,BX14:BX28,BX32:BX46