Only time can be enter in cell Using Data Validation (solved)

[Syedali]

Hi Frds,

Kindly give your advice.
Time only going to enter in Yellow Highlighted cell. How can i prevent other entries.

Sample file attached.

Thanks.

 

[Deepak]

If u are looking for manual entry then press.

Ctrl+Shift+:

& Change the cell format as required.

 

[NARAYANK991]

Hi ,

The Data Validation feature has an option for Time ; can you not make use of that ?

Narayan

 

[Syedali]

If u are looking for manual entry then press.

Ctrl+Shift+:

& Change the cell format as required.

Thanks Narayan and Deepak
Yes. People in my team doing that only. But in some case they have (by mistake) typed or copied from above cell it contains text. So i want prevent using some data validation formula. Kindly advice on this.

 

[Deepak]

In that case you need to implement this..

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'  Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

http://forum.chandoo.org/threads/data-validation-list-override-when-copy-pasted.11933/

http://www.j-walk.com/ss/excel/tips/tip98.htm

 

[NARAYANK991]

Hi ,

I do not know what you are talking about.

If you click on Data Validation , and select Time , you can enter the start time of 00:00 and the end time of 23:59.

If this has been done , how can anyone enter text in these cells ?

Copy + paste will always remove any Data Validation that is present in a cell ; it has nothing to do with whether the DV is through a formula or otherwise.

Narayan

 

[Syedali]

Hi ,

I do not know what you are talking about.

If you click on Data Validation , and select Time , you can enter the start time of 00:00 and the end time of 23:59.

If this has been done , how can anyone enter text in these cells ?

Copy + paste will always remove any Data Validation that is present in a cell ; it has nothing to do with whether the DV is through a formula or otherwise.

Narayan

Thank you

 

[Syedali]

In that case you need to implement this..

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
'  Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

http://forum.chandoo.org/threads/data-validation-list-override-when-copy-pasted.11933/

http://www.j-walk.com/ss/excel/tips/tip98.htm

Thank you