Solved Autodate that cell when another cell is changed(drop list)

October 16, 2013 at 15:25:57
Specs: Windows 7
I have been searching for a while and it seems this is a tough question. VBA seems to be the way to go but I am out of my element there.
We have a shared Excel workbook for tracking production status.
Column C has a drop down list of 39 status choices. (list is on sheet 2 C2:C40 but can change if cells are added)(not sure if you need that.)
We want column D (Sheet 1) to automatically record the date when the status C was changed, We want the cells to be locked to avoid manual changes. The following code is very much on the right track by DerbyDad03:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Cells.Count = 1 Then
Range("D" & Target.Row) = Date
End If
End Sub
It would also be nice to be able to have one undo just in case someone in a given session made a mistake. And if we are going cadillac version, the user ID could be added in column E.
We would greatly appreciate any help or suggestions.
Cheers <clink>

message edited by M-A-S-H


See More: Autodate that cell when another cell is changed(drop list)

Report •

#1
October 17, 2013 at 04:41:49
✔ Best Answer
Your requirements are not clear, at least not to me.

You said "We want the cells to be locked to avoid manual changes." Yet you also say that rows might be inserted, and that the drop down will be changed. If the sheet doesn't allow manual changes, how will those actions be done?

In addition, here's a posting tip...

Before posting code in this forum, please click on the following line and read the instructions found via that link:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 17, 2013 at 13:50:53
Hi DerbyDad03,
Thanks for the reply and the posting tip.
Sorry for not being clear about what is locked. Column C, on sheet 1, is unlocked for edit using a dropdown to limit entries. I think the whole sheet 2 reference is a red herring.

The code you provided works but there are two issues I am trying to balance.
1. Users can't undo if they make a mistake.
2. When Column D is locked the VBA code stalls on the following line:

Range("D" & Target.Row) = Date

When unlocked the code works but then people can change the date, which is great when they make a mistake, but not so great when people create time travelling statuses. Locking is necessary but allowing an undo level or two to allow error correction is also needed. Locking down a user ID stamp is not a bad idea for accountability too.
I hope that clarifies.

Here is code we are currently trying to use:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 And Target.Cells.Count = 1 Then
     Range("D" & Target.Row) = Date
 End If
End Sub

Thanks again,
Cheers (clink, sip)

message edited by M-A-S-H


Report •

#3
October 23, 2013 at 16:07:14
Hi DerbyDad03,
Thanks for the reply and the posting tip.
Sorry for not being clear about what is locked. Column C, on sheet 1, is unlocked for edit using a dropdown to limit entries. I think the whole sheet 2 reference is a red herring.
The code you provided works but there are two issues I am trying to balance.
1. Users can't undo if they make a mistake.
2. When Column D is locked the VBA code stalls on the following line:

Range("D" & Target.Row) = Date

When unlocked the code works but then people can change the date, which is great when they make a mistake, but not so great when people create time travelling statuses. Locking is necessary but allowing an undo level or two to allow error correction is also needed. Locking down a user ID stamp is not a bad idea for accountability too.
I hope that clarifies.

Here is code we are currently trying to use:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 And Target.Cells.Count = 1 Then
     Range("D" & Target.Row) = Date
 End If
End Sub

Thanks again,
Cheers


Report •
Related Solutions


Ask Question