Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
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 "Sharon" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
On Apr 15, 7:32 pm, Barb Reinhardt
wrote: 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 "Sharon" wrote: 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- Hide quoted text - - 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
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" wrote: On Apr 15, 7:32 pm, Barb Reinhardt wrote: 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 "Sharon" wrote: 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- Hide quoted text - - 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event programming
On Apr 16, 4:12 am, Barb Reinhardt
wrote: 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" wrote: On Apr 15, 7:32 pm, Barb Reinhardt wrote: 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 "Sharon" wrote: I need to use theChangeEventfor 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 achangeeventon 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- Hide quoted text - - 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?- Hide quoted text - - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event Programming - Cell Value change - 2 sheets involved - VBA - Excel | Excel Programming | |||
Excel VBA Programming...How to Trigger an Event WhenYou Move Off a Cell | Excel Programming | |||
need programming help with nested event codes | Excel Programming | |||
programming the VBE for a new event procedure | Excel Programming | |||
Event Procedure Programming | Excel Programming |