VBA Timestamp Help

[tinaluc]

Hi,
I have found a few different VBA codes that shows how to insert a time stamp on a cell once a certain cell has changed, which is a tracking change VBA. The problem with that is, it will only create a time stamp if someone goes in and actually types in a different value, not if it is a formula and the formula causes the value to change. Can anyone suggest another solution? I have the VBA code that I am using below.

A simple example is:
Cell A1 contains a drop-down of A or B
Cell B2: =if(a1="A","apple","ball")
Cell C2 would show the time-stamp of when someone selects a different option in cell A1

---------------

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
'if anything in column B changes
Cells(Target.Row, 3).Value = Now
'inserts the time stamp in column 3
End If

End Sub

 

[JoeMo]

For your example try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Cells(Target.Dependents.Row, 3).Value = Now
Application.EnableEvents = True
End Sub

 

[tinaluc]

What if I just want to make a VBA to look and see if the value of the formula change and if it does it will put a times tamp on the cell next to it?
So if the value in column Q changes due the formula, insert a time stamp in column R.
It doesnt matter what the value is, as long as there is any type of value in it, except when it is empty.

 

[JoeMo]

What if I just want to make a VBA to look and see if the value of the formula change and if it does it will put a times tamp on the cell next to it?
So if the value in column Q changes due the formula, insert a time stamp in column R.
It doesnt matter what the value is, as long as there is any type of value in it, except when it is empty.

Did you try the code I posted, adapted to your columns?

 

[tinaluc]

Yes I did and it didnt work.

 

[JoeMo]

Yes I did and it didnt work.

That's not a helpful description. Can you provide some more information? Did you get an error message or ..... ? Did you use the code exactly as posted (which was setup to handle your simple example in post #1) or did you adapt it in some way? If adapted, can you post the adaptation?

 

[tinaluc]

The code I used was:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$L$1" Then Exit Sub
Application.EnableEvents = False
Cells(Target.Dependents.Column, 17).Value = Now
Application.EnableEvents = True
End Sub

$L$1 had the word Colder in it.

The 2 text options are Warmer or Colder. The idea is depending on the formula, it will provide one of the 2. When there is text or if the text changes, we need a timestamp.
I has the formula in cell P7:
=if(Q7<>"",now(),"")

That does what I need it to do except it updates all the time and I need it to not update.

 

[JoeMo]

The code I used was:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$L$1" Then Exit Sub
Application.EnableEvents = False
Cells(Target.Dependents.Column, 17).Value = Now
Application.EnableEvents = True
End Sub

$L$1 had the word Colder in it.

The 2 text options are Warmer or Colder. The idea is depending on the formula, it will provide one of the 2. When there is text or if the text changes, we need a timestamp.
I has the formula in cell P7:
=if(Q7<>"",now(),"")

That does what I need it to do except it updates all the time and I need it to not update.

Does L1 have a formula in it or is it a drop down with choices Warmer and Colder?
Where (what cell) is the formula and what is the exact formula?

This line:
Cells(Target.Dependents.Column, 17).Value = Now
is different than what I posted - you probably want Column to be Row, but I can't be sure until you answer my questions and give a clear explanation of what exactly you want to trigger a time stamp and where exactly you want that stamp to be placed. Why not post a sample of your sheet layout and the result you are looking for?

 

[tinaluc]

I tried to attatch a picture but it wouldnt let me so I am trying this way instead.
The spreadsheet works is:
F2 is a dropdown of options, low, med, high.
Q7 formula is: =IF(N7="","",IF(AND(N7>$H7,P7>$J7),"WARMER",IF(AND(N7<$G7,P7<$I7),"COLDER","")))

so how it works is: if the user switches from the Low Med High options in cell F2, there can be more colder or warmers in column Q and anytime that changes, I would like a timestamp to show up right next to it.
The data comes in through a spreadsheet that updates throughout the day so I can't use a formula of =now() since I dont want it to update.

Dose that clear it up?


https://plus.google.com/u/0/photos/109835279397223612223/albums/6109037530359574209

 

[NoSparks]

the worksheet_change event does not fire when a value changes due to a formula you need to use the calculate event
have a look at this to see a way of dealing with changes via formula
excel - VBA code doesn't run when cell is changed by a formula - Stack Overflow