Update a date cell when a target cell change....

[chongcc]

I am try to automatic update a date cell when the target cell changes.
A1 is what I need, A2 is what I had, A3 is the inventory date. I want to
made it whenever A2 change, A3 will automatic update to the current date. If
not, A3 should stay unchange with the last inventory date.

Thanks in advance for your help.

 

[JLatham]

This goes into the worksheet's _Change event code. To get there, right-click
on the sheet tab and choose [View Code] from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then
Exit Sub
End If
Range("A3") = Now()
End Sub

If you need to deal with cells other than A2 and A3, then we need to know a
little more about how your worksheet is laid out.

 

[chongcc]

Yes, that help. Got another question if you don't mind. I had A1:B12 locked
and protect, and the code can't write to those cells. How can I unlock it,
write to it and lock it again. Thanks.

This goes into the worksheet's _Change event code. To get there, right-click
on the sheet tab and choose [View Code] from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then
Exit Sub
End If
Range("A3") = Now()
End Sub

If you need to deal with cells other than A2 and A3, then we need to know a
little more about how your worksheet is laid out.

I am try to automatic update a date cell when the target cell changes.
A1 is what I need, A2 is what I had, A3 is the inventory date. I want to
made it whenever A2 change, A3 will automatic update to the current date. If
not, A3 should stay unchange with the last inventory date.

Thanks in advance for your help.

 

[JLatham]

Do the unprotect and protect within the code, as

ActiveSheet.Unprotect
Range("A3") = Now()
ActiveSheet.Protect

If the sheet is password protected, then
ActiveSheet.Unprotect Password:="thepassw0rd"
Range("A3") = Now()
ActiveSheet.Protect Password:="thepassw0rd"

Yes, that help. Got another question if you don't mind. I had A1:B12 locked
and protect, and the code can't write to those cells. How can I unlock it,
write to it and lock it again. Thanks.

This goes into the worksheet's _Change event code. To get there, right-click
on the sheet tab and choose [View Code] from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then
Exit Sub
End If
Range("A3") = Now()
End Sub

If you need to deal with cells other than A2 and A3, then we need to know a
little more about how your worksheet is laid out.

I am try to automatic update a date cell when the target cell changes.
A1 is what I need, A2 is what I had, A3 is the inventory date. I want to
made it whenever A2 change, A3 will automatic update to the current date. If
not, A3 should stay unchange with the last inventory date.

Thanks in advance for your help.

 

[chongcc]

That's just what I am looking for and it works great. Thanks a lot.

Do the unprotect and protect within the code, as

ActiveSheet.Unprotect
Range("A3") = Now()
ActiveSheet.Protect

If the sheet is password protected, then
ActiveSheet.Unprotect Password:="thepassw0rd"
Range("A3") = Now()
ActiveSheet.Protect Password:="thepassw0rd"

Yes, that help. Got another question if you don't mind. I had A1:B12 locked
and protect, and the code can't write to those cells. How can I unlock it,
write to it and lock it again. Thanks.

This goes into the worksheet's _Change event code. To get there, right-click
on the sheet tab and choose [View Code] from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then
Exit Sub
End If
Range("A3") = Now()
End Sub

If you need to deal with cells other than A2 and A3, then we need to know a
little more about how your worksheet is laid out.

:

I am try to automatic update a date cell when the target cell changes.
A1 is what I need, A2 is what I had, A3 is the inventory date. I want to
made it whenever A2 change, A3 will automatic update to the current date. If
not, A3 should stay unchange with the last inventory date.

Thanks in advance for your help.

 

[JLatham]

I thought it might be. You're welcome and thank you for the thank you and
letting us know it worked.

That's just what I am looking for and it works great. Thanks a lot.

Do the unprotect and protect within the code, as

ActiveSheet.Unprotect
Range("A3") = Now()
ActiveSheet.Protect

If the sheet is password protected, then
ActiveSheet.Unprotect Password:="thepassw0rd"
Range("A3") = Now()
ActiveSheet.Protect Password:="thepassw0rd"

Yes, that help. Got another question if you don't mind. I had A1:B12 locked
and protect, and the code can't write to those cells. How can I unlock it,
write to it and lock it again. Thanks.

:

This goes into the worksheet's _Change event code. To get there, right-click
on the sheet tab and choose [View Code] from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then
Exit Sub
End If
Range("A3") = Now()
End Sub

If you need to deal with cells other than A2 and A3, then we need to know a
little more about how your worksheet is laid out.

:

I am try to automatic update a date cell when the target cell changes.
A1 is what I need, A2 is what I had, A3 is the inventory date. I want to
made it whenever A2 change, A3 will automatic update to the current date. If
not, A3 should stay unchange with the last inventory date.

Thanks in advance for your help.