Hey everyone! First, thanks for looking at my problem and being so willing to assist. I have the code below that consists of 2 parts. The first part is a date/time stamp with a user name in final 2 columns. And the 2nd part is a requirement that columns T & U are only entered in date format. This may seem trivial, but if you enter anything other than a date in T/U, the dialog box appears, and tells you it must be a date, however, the final 2 columns still record the date/time stamp and user name. If for some reason the user does not enter the date at that time, I do not want the date/time stamp and user name for that row. Only later when they come back and enter it.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column >= 12 Then ' <!-- Column L or higher is trigger
Range(Cells(Target.Row, 27), Cells(Target.Row, 27)).Value = Date + Time ' <!--- Need Date & Time
Range(Cells(Target.Row, 27), Cells(Target.Row, 27)).NumberFormat = "m/d/yyyy h:mm AM/PM" ' <!--- Format for Date/Timestamp
Range(Cells(Target.Row, 28), Cells(Target.Row, 28)) = Application.UserName ' <!--- Username
End If
ErrHandler:
Application.EnableEvents = True
Set w = ActiveSheet.Range("T:U") ' <!--- Columns that use dates & have data validations
For Each c In w
If c.Value <> "" And Not IsDate(c) Then
c.ClearContents
MsgBox "Only a date format is permitted in this cell."
End If
Next c
End Sub
Hi Sherry,
A few tips:
Range(Cells(Target.Row, 27), Cells(Target.Row, 27)).Value is not the right way, as both ranges point to the same cell. Just use Cells(Target.Row, 27).Value
I don't understand this line:
Set w = ActiveSheet.Range("T:U") ' <!--- Columns that use dates & have data validations
Basically, you check 2 full columns at every cell change... That's over 2 million cells to check at each cell change!
Check just current row, that's where you can use Range between 2 cells, more, you can do that check ONLY if the change is in T/U columns:
Set w = ActiveSheet.Range(Cells(Target.Row, "T"), Cells(Target.Row, "U"))
If Not Intersect(Target, w) is nothing then
If Target.Value <> "" And Not IsDate(Target) Then
Target.ClearContents
MsgBox "Only a date format is permitted in this cell."
End If
End If
Another one:
"If Target.Column >= 12 Then" will trigger the code even when a user makes a change in columns 27 or 28, where in fact the code is supposed to write. It makes sense to exclude these columns from triggering the code:
If Target.Column >= 12 Then should be:
Dim Col As Long: Col = Target.Column
If (Col >= 12 And Col < 20) Or (Col > 21 and Col<27) Then
All together, the code should look like this:
If Target.Row = 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim Col As Long: Col = Target.Column
If (Col >= 12 And Col < 20) Or (Col > 21 and Col<27) Then ' exclude columns T,U,27,28 or higher
Cells(Target.Row, 27).Value = Date + Time ' <!--- Need Date & Time
Cells(Target.Row, 27).NumberFormat = "m/d/yyyy h:mm AM/PM" ' <!--- Format for Date/Timestamp
Cells(Target.Row, 28).Value = Application.UserName ' <!--- Username
End If
Set w = ActiveSheet.Range(Cells(Target.Row, "T"), Cells(Target.Row, "U"))
If Not Intersect(Target, w) is nothing then
If Target.Value <> "" And Not IsDate(Target) Then
Target.ClearContents
MsgBox "Only a date format is permitted in this cell."
End If
End If
Application.EnableEvents = True
@Catalin Bombea,
That is quite helpful, and I understand about the 2 million cells, I am not sure how I overlooked that. For this worksheet ("data") there are 501 rows of data. This is used as a template, so although it may change it would only be once every few months when I prep the template with the new data data (via Power Query), and that can be changed as needed of course.
But there is one more thing, if a value that is NOT a date is entered into T/U (T2:U501), then the error dialog appears and says this muist be a date. AND Columns AA/AB the Date/Time Stamp and User Name are not populate. This IS correct. However, if the user goes back an corrects column T/U with the date as required, then Columns AA/AB the Date/Time Stamp and User Name SHOULD be populated as it is the correct data.
I am sorry if I did not communicate that correctly. But everything else is perfect. Thanks so much!
You can use this structure:
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim Col As Long: Col = Target.Column
If (Col >= 12 And Col < 20) Or (Col > 21 and Col<27) Then ' exclude columns T,U,27,28 or higher
FillTimeStamp Target
End If
Set w = ActiveSheet.Range(Cells(Target.Row, "T"), Cells(Target.Row, "U"))
If Not Intersect(Target, w) is nothing then
If Target.Value <> "" And Not IsDate(Target) Then
Target.ClearContents
MsgBox "Only a date format is permitted in this cell."
Else
FillTimeStamp Target
End If
End If
Application.EnableEvents = True
The procedure used in code above is:
Target.Parent.Cells(Target.Row, 27).Value = Date + Time ' <!--- Need Date & Time
Target.Parent.Cells(Target.Row, 27).NumberFormat = "m/d/yyyy h:mm AM/PM" ' <!--- Format for Date/Timestamp
Target.Parent.Cells(Target.Row, 28).Value = Application.UserName ' <!--- Username
End Sub
@Catalin Bombea,
PERFECTION!!!! Thanks so very much! I never would have figured this one out without your assistance!
