worksheet change event help

[gschwint]

I want to trigger a worksheet change event whenever cells in this worksheet (Sheet 1) change. For example, the cells in (sheet 1) are automatically changed based on information in previous worksheets so if the data in previous worksheets change I want the worksheet change event to run in (sheet 1). Maybe I am using the wrong event??? I tried a worksheet calculate event and that didn't make a difference. Any ideas???

 

[gschwint]

I thought it may be good to show the code I have.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Integer
Dim X As Double
Dim Y As Double

    
    If Not Intersect(Target, Range("E17:E36")) Is Nothing And Range("A" & Target.Row) <> "" And Range("G" & Target.Row) <> "" Then
    ActiveSheet.Unprotect
        If Range("G" & Target.Row) = "Corn Silage" Then
            R = Target.Row
            X = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:B17"))
            Y = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:C17"))
            Range("N" & R).Value = (X - Y * Range("E" & R)) * (Range("M" & R) * 8)
        Else
            R = Target.Row
            X = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:B17"))
            Y = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:C17"))
            Range("N" & R).Value = (X - Y * Range("E" & R)) * Range("M" & R)
        End If
        If Range("N" & R).Value < 0 Then
        Range("N" & R).Value = 0
        End If
    ActiveSheet.Protect
    End If

    If Not Intersect(Target, Range("E43:E57")) Is Nothing And Range("A" & Target.Row) <> "" And Range("G" & Target.Row) <> "" Then
    ActiveSheet.Unprotect
        R = Target.Row
        X = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:B17"))
        Y = Application.WorksheetFunction.Lookup(Sheets("K management").Range("G" & R), Sheets("K Equations").Range("A1:C17"))
        Range("N" & R).Value = (X - Y * Range("E" & R)) * Range("M" & R)
        If Range("N" & R).Value < 0 Then
        Range("N" & R).Value = 0
        End If
    ActiveSheet.Protect
    End If

End Sub

 

[erik.van.geit]

gschwint,

Worksheet_Change is triggered when different conditions are met:

1. the sheet itself must be active at the moment of the change.
2. a formula or value has to be changed (the result of a change on another page will not trigger the event: f.i. when you perform a manual calculation hitting F9)

suppose sheet1 has within cell A1 =sheet2!A1
when you're on sheet2 and you change A1 only the Worksheet_Change-module of sheet2 will be activated, not the sheet1-module

kind regards,
Erik

 

[gschwint]

Is there a way to use the worksheet activate event to force a worksheet change event. I though that if I tired using the following code in a worksheet activate event it might trigger the change event but it didn't work. Is there any hope that something like this may work?

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
Range("A1").Activate
With ActiveCell
    .Value = 0
End With
ActiveSheet.Protect

End Sub

Alternatively is there a way to create a macro that would be assigned to a button to trigger all events in the workbook?

Thanks

George