restrict cell format in a given column

[TG2812]

Hello,

I'm trying to come up with a small macro allowing to restrict the user input to a given date format in column B. If it is not a date, then content is cleared and a pop up message appear to alert the user. Ultimately all date format should align to mm/dd/yyyy (the macro could auto correct wrong date input). Any idea how to build such code?

I initially wanted to insert Microsoft date and time picker control but I do not have the right version of excel installed (using 64 bits)..add-ins are also not a solution as it would require the other users to have it installed on their PCs. Hence why the macro is a viable alternative in my case.

Thank you in advance for your help .

 

[jasonb75]

Why not just use data validation?

 

[TG2812]

@jasonb75 thank you for the prompt reply. I tried data validation but I cannot apply the formula to a specific column only (or at least I haven't found a way to do it).

=AND(ISNUMBER(B2),LEFT(CELL("format",B2),1)="D")

Alternatively, i came up with this macro but the worksheet event is not correct I thin. For example when i enter text value and hit enter, nothing happens.

Private Sub Worksheet_Activate()
If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
Set c = ActiveCell
If c.Value <> "" And Not IsDate(c) Then
c.ClearContents
MsgBox "Only a date format is permitted in this cell."
End If
End If
End Sub

 

[jasonb75]

Don't use a formula for validation, just set a min and max date range with the correct format already applied to the cells, then protect the sheet.

 

[TG2812]

@jasonb75 works like a charm. Thank you.

Just for my curiosity, what would have been the solution with a macro?

 

[jasonb75]

what would have been the solution with a macro?

Worksheet_Change might work, although I would be reluctant to call it a solution.