worksheet change event help

gschwint

Board Regular
Joined
Aug 17, 2004
Messages
121
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???
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

gschwint

Board Regular
Joined
Aug 17, 2004
Messages
121
I thought it may be good to show the code I have.

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Aug 17, 2004
Messages
121
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?

Code:
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
 

Forum statistics

Threads
1,147,507
Messages
5,741,564
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top