help

A

Angel

my question is
example
that if from sheet 1
i want to get value( numer) data if >0
display to another sheet the date when was insert

Regards

Thanks
for the help!
 
J

JLatham

This cannot be done accurately with any built in functions or data. There is
a document property that tells when the entire file was last modified, but
that may not be the same as when the data on Sheet1 was added to it. More
than likely it will be the last time you either closed or re-opened it.

You could do this with code attached to Sheet1's worksheet _Change event to
put the date/time that a change was made somewhere on that sheet or elsewhere
in the workbook, but if you want details of changes for several cells, then
it takes one storage location for each cell you want to track changes for.

The code below tracks the change/value in one single cell on Sheet1. Let's
say you want to watch/test for changes in cell A5 on Sheet1, and you want the
date to appear/not appear in B5 on another sheet (Sheet2) then you could put
code like this in the worksheet's _Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range
Set iSect = Application.Intersect(Range(Target.Address), Range("A5"))
If Application.Intersect(Range(Target.Address), Range("A5")) Is Nothing
Then
Exit Sub
End If
If Range(Target.Address) > 0 Then
Sheets("Sheet2").Range("B5") = Now() ' save altered date/time
Else
Sheets("Sheet2").Range("B5") = "" ' clear it out
End If
End Sub

To enter this code into the proper place for it to work with sheet 1,
right-click on the sheet's name tab and choose View Code. Then cut this code
and paste it into the page that appeared. Change the sheet names and cell
references as needed for your real world use.
 
J

JLatham

NOTE: I see that the editor here broke up one line in the code

The single word "Then" should be on the same line with the code above it so
that that line ends with
Is Nothing Then
 
A

Angel

it is a like complicaded

the best way is to see an example, in the excel
file i put this formula in the visuals,
i don't get it in the target. address
i put the the location of the data 'sheet'!I6
so i get me an error

my other question
can i do somethig more easy, like when i want
to display the iinformation, because i never use the
visual

regards

thanks for time

if you can send an example in excel
i will great mi email
[email protected]

If Range(target.part1I6) > 0 Then
Sheets("Web").Range("G4") = Now() '
Else
Sheets("Web").Range("G4") = "" '
End If
End Sub
 
J

JLatham

In your code, I believe that
Range(target.part1I6)
should be
Range(Target.Address)

But I cannot be certain that you have the code in the correct place. I am
emailing you a workbook that has code in the proper place that will show you
how to do this.
 
J

JLatham

For anyone following this, here's the solution that was worked out. Workbook
has 4 sheets this needs to be done from, with a fifth one sheet collecting
all the dates of when things happened on the other 4 - tracking 91 dates on
the 4 sheets:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range
Dim ChangedInRow As Long
Dim DifferenceInRowNumbers As Long

Set iSect = Application.Intersect(Range("F:F"), Range(Target.Address))
If iSect Is Nothing Then
' no change in column F
Exit Sub
End If
If iSect.Cells.Count > 1 Then
'more than 1 cell selected
'do nothing
Exit Sub
End If
'change for each sheet
'source locations start on row 6, destination on row 4
' 4 worksheets involved, each has different offset to the destination row
DifferenceInRowNumbers = 4 - 6 ' row on Web - row on this sheet

Application.EnableEvents = False
ChangedInRow = iSect.Row
If IsNumeric(iSect) And iSect > 0 Then ' it is a number
ThisWorkbook.Worksheets("Web").Range("D" & (ChangedInRow +
DifferenceInRowNumbers)).Value = Format(Now(), "m/d/yy")
Else
ThisWorkbook.Worksheets("Web").Range("D" & (ChangedInRow +
DifferenceInRowNumbers)).Value = ""
End If
Application.EnableEvents = True

End Sub
 
Top