Change Event programming

[Sharon]

I need to use the Change Event for the following

Find the cell that changed on worsheet1 (easy (target.address))

Find the formula in worksheet2, column B that is referencing the
target.address in worksheet1.
store the time in column c on worksheet2 on the same row as the
formula

I've been chasing my tale on this and appreciate any and all feedack

THANKS

Private Sub Worksheet_Change(ByVal Target As Range)
' Intercept a change event on the form
MsgBox "Range " & Target.Address & " was changed"
'
' for example - Form B4 is referenced by B11 in the upload
spreadsheet
' the formula in B11 is '=Form!$B$4
' Form F4 is referenced by B23 in the upload
spreadsheet (=Form!$F$23)
'
' use absolute formulas

' store the date (now()) in the column E of the same row with the
formula in the upload sheet
'store the word 'changed' in column F in the upload sheet
' for example, E and F of row 11 in upload ... when Form B4
changes
End Sub

 

[Barb Reinhardt]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim lrow As Integer
Dim ChangeAddress As String
'Address that was changed
ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
Target.Column & "*"
Debug.Print ChangeAddress

lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Debug.Print lrow
Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)

For Each r In myRange
Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
ChangeAddress
If Not IsEmpty(r) Then
If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
Debug.Print "Formula links to changed cell at address " &
r.Address
r.Offset(0, 4).Value = Now
r.Offset(0, 5).Value = "Changed"
End If
End If
Next r
End Sub

 

[JMB]

Another suggestion you may be able to work with. Note that you requested to
search column B and put the time in column C, but your code comments indicate
you want the time in Column E. I used B and C.

You will need to change the worksheet name for rngSearch to whatever your
sheet is actually named. Watch for word wrap and be sure to back up before
trying.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCriteria As String
Dim rngSearch As Range
Dim rngFound As Range

strCriteria = Replace(Target.Address(True, True, xlA1, True), _
"[" & ThisWorkbook.Name & "]", "", 1, 1, vbTextCompare)
Set rngSearch = Sheets("Sheet2").Columns(2) '<<<CHANGE

With rngSearch
Set rngFound = .Find( _
What:=strCriteria, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngFound Is Nothing Then
With rngFound.Parent.Range("C" & rngFound.Row)
.Value = Now
.NumberFormat = """Changed ""m/d/yyyy h:mm:ss AM/PM"
End With
End If

End Sub

 

[Sharon]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim lrow As Integer
Dim ChangeAddress As String
'Address that was changed
ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
Target.Column & "*"
Debug.Print ChangeAddress

lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Debug.Print lrow
Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)

For Each r In myRange
Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
ChangeAddress
If Not IsEmpty(r) Then
If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
Debug.Print "Formula links to changed cell at address " &
r.Address
r.Offset(0, 4).Value = Now
r.Offset(0, 5).Value = "Changed"
End If
End If
Next r
End Sub









- Show quoted text -

I must be missing something truly elementary. I've tried both (solving
the word wrap and changing the sheet name). No errors but it doesn't
find a match and it should. How can I post my workbook?

 

[Barb Reinhardt]

I've noticed that if you don't put a space between the underscore and the
last character of the line, you'll get an error with the line wrap. Is this
what you're talking about?

Why don't you step through it and see where it croaks. Are you sure you've
got all the spaces in the sheet names?

 

[Sharon]

I've noticed that if you don't put a space between the underscore and the
last character of the line, you'll get an error with the line wrap. Is this
what you're talking about?

Why don't you step through it and see where it croaks. Are you sure you've
got all the spaces in the sheet names?






- Show quoted text -

Thank you for your thorough response. It turns out that as the module
executed, Excel decided to go off an execute another module when it
was only 1/2 way through the change event module. I removed the other
module and now excel happily completes and I have the values I need.
Whew! I ended up calling MrExcel. They were very professional.