Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
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" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
Did you add the code to have it do that?
I just gave you an outline/general direction on how to approach the problem. -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
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" wrote: Did you add the code to have it do that? I just gave you an outline/general direction on how to approach the problem. -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
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 -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: Did you add the code to have it do that? I just gave you an outline/general direction on how to approach the problem. -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date stamping based on cell value
Tom,
Thanks!!! Bob "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: Did you add the code to have it do that? I just gave you an outline/general direction on how to approach the problem. -- Regards, Tom Ogilvy "Bob" wrote: 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" wrote: 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 -- Regards, Tom Ogilvy "Bob" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic date stamping | Excel Discussion (Misc queries) | |||
Date stamping a cell change | Excel Discussion (Misc queries) | |||
Date and time stamping with a (macro) button | Excel Worksheet Functions | |||
Time Stamping A Cell | Excel Discussion (Misc queries) | |||
Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions |