Date stamping based on cell value

[Bob]

I have a worksheet containing project-related information. In particular:

Column D = Status Code (0 - 20); Code "11" = On-Hold, Code "12" = Cancelled
Column DT = Project Hold Date
Column DU = Project Un-Hold Date
Column DV = Project Cancelled Date
Column DW = Project Un-Cancelled Date

I'm trying to write a macro whereby if a user inputs Code "11" in Column D
for a given project, the current date would automatically be entered in
Column DT. Later on, if the Code is changed to anything but Code 11 (except
for Code 12), the current date would automatically be entered into Column DU.

If a user inputs Code "12" in Column D for a given project, the current date
would automatically be entered in Column DV. Later on, if the Code is
changed to anything but Code 12 (except for Code 11), the current date would
automatically be entered into Column DW.

Also, it would be nice if the macro displayed an error message if a user
attempts to indicate that a project is on Hold or Cancelled more than once
(i.e., check to see if columns DT - DW already contain a date).

Any help would be greatly appreciated.
Thanks,
Bob

 

[Tom Ogilvy]

This is a basic approach using the change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDU As Range, rngDV As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Target.Column = 4 And Target.Row > 1 Then
Set rngDU = Cells(Target.Row, "DU")
Set rngDV = Cells(Target.Row, "DV")
Select Case True
Case Target = 11 And IsEmpty(rngDU)

Case Target <> 12 And Not IsEmpty(rngDU)

End Select
End If

End Sub

You know your conditions better than I do. You can continue with the select
case paradigm or revert to an If - Then - Else structure. Work the logic
you know into the procedure and post back if you have a specific question.
You would right click on the sheet tab and select view code, then enter you
code there. (You can select the change event from the dropdowns at the top
of the module Worksheet in the left, Change in the right).

http://www.cpearson.com/excel/events.htm

 

[Bob]

Tom,

Thanks for your help. Unfortunately, when I input an "11" in cell D6, for
example, the current date does not get automatically inputted into cell DT6.
And then when I change the value in cell D6 to "9", for example, the current
date does not get automatically inputted into cell DU6.

Bob

 

[Tom Ogilvy]

Did you add the code to have it do that?

I just gave you an outline/general direction on how to approach the problem.

 

[Bob]

Tom,
No I didn't. I don't know how. I'm a novice at VBA. Hence the reason why
I reached out to this Discussion Group.
Bob

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Target.Column = 4 And Target.Row > 1 Then
v = Target.Value
On Error GoTo ErrHandler
Application.EnableEvents = False
Application.Undo
vold = Target.Value
Target = v
If v = 11 Or v = 12 Then
If v = 11 And IsDate(Cells(Target.Row, "DT")) Then
Target.Value = vold
MsgBox "Not allowed"
Application.EnableEvents = True
Exit Sub
ElseIf v = 12 And IsDate(Cells(Target.Row, "DV")) Then
Target.Value = vold
MsgBox "Not Allowed"
Application.EnableEvents = True
Exit Sub
End If
End If

If vold = 11 Then
If v <> 12 Then
Cells(Target.Row, "DU").Value = Date
End If
ElseIf vold = 12 Then
If v <> 11 Then
Cells(Target.Row, "DW").Value = Date
End If
ElseIf v = 11 Then
Cells(Target, "DT").Value = Date
ElseIf v = 12 Then
Cells(Target, "DV").Value = Date
End If

End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Bob]

Tom,
Thanks!!!
Bob

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Target.Column = 4 And Target.Row > 1 Then
v = Target.Value
On Error GoTo ErrHandler
Application.EnableEvents = False
Application.Undo
vold = Target.Value
Target = v
If v = 11 Or v = 12 Then
If v = 11 And IsDate(Cells(Target.Row, "DT")) Then
Target.Value = vold
MsgBox "Not allowed"
Application.EnableEvents = True
Exit Sub
ElseIf v = 12 And IsDate(Cells(Target.Row, "DV")) Then
Target.Value = vold
MsgBox "Not Allowed"
Application.EnableEvents = True
Exit Sub
End If
End If

If vold = 11 Then
If v <> 12 Then
Cells(Target.Row, "DU").Value = Date
End If
ElseIf vold = 12 Then
If v <> 11 Then
Cells(Target.Row, "DW").Value = Date
End If
ElseIf v = 11 Then
Cells(Target, "DT").Value = Date
ElseIf v = 12 Then
Cells(Target, "DV").Value = Date
End If

End If
ErrHandler:
Application.EnableEvents = True
End Sub