VBA force only date format in a certain cell on a certain sheet

[willow1985]

Hello,

I am trying to have a certain cell locked (B2) that only a certain date format can be entered but I keep getting an error with the code. Perhaps you can shed some light on what I am doing wrong? Still very new to this.

Thank you

Private Sub Worksheet_Change()

    Call ValidateDate(2) 'For Column B2:B2




End Sub




Private Sub ValidateDate()


    Set r = ActiveSheet.Range(Cells(2, Col), Cells(2, Col))
    For Each c In r
        If c.Value <> "" And Not IsDate(c) Then
            c.ClearContents
            MsgBox "Please enter Date Returned in the following format: MM/DD/YYYY"
        End If
    Next c
ActiveSheet.Range("B2:B2").NumberFormat = "[$-409]d-mmm-yy;@"
End Sub

 

[willow1985]

Sorry I did not clarify, I am using your code. The event activation you provided just needed an End Sub instead of Exit Sub. It is working now. Thank you for your help.

 

[Joe4]

The event activation you provided just needed an End Sub instead of Exit Sub

Ah, yes. Sorry about that!

Glad to hear things are working.

One of the advantages to the code that I provided is that it is easy to change it to work on any range, whether it be a single cell, whole column, or other shaped range.
Simply change the range value in the top procedure, and everything will flow off of that.