restrict cell format in a given column

TG2812

Board Regular
Joined
Apr 15, 2015
Messages
176
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 .
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
Why not just use data validation?
 

TG2812

Board Regular
Joined
Apr 15, 2015
Messages
176
@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

Well-known Member
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 15, 2015
Messages
176
@jasonb75 works like a charm. Thank you.

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

Watch MrExcel Video

Forum statistics

Threads
1,118,985
Messages
5,575,381
Members
412,658
Latest member
LS0009
Top