restricting data entry to non-times

[jbrass]

I actually need to have entries in some cells to be numbers or text
but NOT times.
Thus I need the cells formatted as General. General formatting makes
10:00 show up as 0.416667. I need data entries to be restricted to
non-
times. Can I have data validation set up to restrict time entries only
but still be able to enter numbers that can be used in formulas?
I have tried =Right(A1,3)=":" in the custom setting under data
validation but
that doesn't seem to workThanks

 

[Gary''s Student]

This little macro will do the same thing:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String

If Intersect(Target, Range("G16")) Is Nothing Then
Exit Sub
End If

s = Target.NumberFormat
If InStr(1, s, ":") > 0 Then
Application.EnableEvents = False
Target.Clear
Target.Select
Application.EnableEvents = True
MsgBox ("Times not allowed")
End If
End Sub

This sample checks entries in cell G16.

If the user types an entry that Excel recognizes as a time, Excel will
change the format from General to a time-style format. The macro looks for a
colon in the cell format and responds accordingly.

If text is entered rather than time:

HELLO:WORLD

the macro will allow it.

 

[ShaneDevenshire]

Hi,

My first attempt to Post this appeared to fail, so here goes again:

You can also use the following macro which incorporates a couple of other
potentially useful ideas:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err1
If Intersect(Target, [A1:B30]) Is Nothing Then Exit Sub
With Target
If Left(.NumberFormat, 1) = "h" Then
Application.EnableEvents = False
.Clear
.Select
MsgBox "Times are not allowed."
End If
End With
Err1:
Application.EnableEvents = True
End Sub