Static date

Question

QUESTION: I've copied the following code from a previous posting, but was wondering if it's possible to skip the first row.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then

  If Target.Column = 2 Then
    If Len(Trim(Target)) = 0 Then
      Me.Unprotect Password:="ABC"
      Target.Offset(0, -1).ClearContents
    Else
      Me.Unprotect Password:="ABC"
      Target.Offset(0, -1).NumberFormat = "mm/dd/yy;@"
      Target.Offset(0, -1) = Date
    End If
  End If

End If Exit Sub End Sub

ANSWER: Erik,

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then

 If Target.Column = 2 and Target.row > 1 Then
   If Len(Trim(Target)) = 0 Then
     Me.Unprotect Password:="ABC"
     Target.Offset(0, -1).ClearContents
   Else
     Me.Unprotect Password:="ABC"
     Target.Offset(0, -1).NumberFormat = "mm/dd/yy;@"
     Target.Offset(0, -1) = Date
   End If
 End If

End If

End Sub

do you want to reprotect the sheet after entering the data?

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then

 If Target.Column = 2 and Target.row > 1 Then
   If Len(Trim(Target)) = 0 Then
     Me.Unprotect Password:="ABC"
     Target.Offset(0, -1).ClearContents
     Me.Protect Password:="ABC"
   Else
     Me.Unprotect Password:="ABC"
     Target.Offset(0, -1).NumberFormat = "mm/dd/yy;@"
     Target.Offset(0, -1) = Date
     Me.Protect Password:="ABC"
   End If
 End If

End If

End Sub

---------- FOLLOW-UP ----------

QUESTION: Basically what I'm doing is attempting to add a static date stamp once the B column cell adjacent to the stamp is populated. I want it to start a specific number of rows down so I have room for column titles and instructions to the users.

I do not need to protect the sheet, the only reason I would do that would be to restrict the user from modifying the column titles and instructions.

Answer

Erik,

OK. If you are not protecting the sheet then you shouldn't need code in the Event to unprotect the sheet.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then ' next statement Target.Row > 1 says react if the target is below row 1 ' change the 1 to a higher number if you want it to start lower

If Target.Column = 2 and Target.row > 1 Then
  If Len(Trim(Target)) = 0 Then

' Me.Unprotect Password:="ABC" <=== not needed if sheet not protected

    Target.Offset(0, -1).ClearContents
  Else

' Me.Unprotect Password:="ABC" <== not needed if sheet not protected

    Target.Offset(0, -1).NumberFormat = "mm/dd/yy;@"
    Target.Offset(0, -1) = Date
  End If
End If

End If

End Sub