Worksheet Change Event has gone AI :o

[Poundland]

Hi all,

Once again I call upon your collective knowledge to solve an issue I have.

Some background on my issue, and what I want to do.

I have created 3 Worksheets, that have different data on each, but have common inputs across all three, I am attempting to create worksheet events so that when one worksheet input is changed it also changes all the other worksheet inputs but can be affected on any of the sheets.

Quick Example;

Sheet "Monthflow", Input into the Sku cell creates a Vlookup in the MSKU cell on same sheet, which in turn updates the same values in the Sku cells and MSKU cells in Sheet "Weekflow" and "Lineflow". Changing the same cell on the "Weekflow" subsequently updates "Monthflow" and "Lineflow".

I hope you are keeping up..

I have used the Enable Events function to switch off Event Changes between updates, and this works fine when inputting into Sku cell on "Monthflow", however on "Weekflow", as soon as I switch the active cell to the Sku cell the Worksheet Event change starts before I have even entered any data, and I cannot understand why this is happening.

My code is below;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 1 And Target.Count = 1 Then Call sku_month
If Target.Row = 3 And Target.Column = 4 Or Target.Column = 5 And Target.Count = 1 Then Call po_month
If Target.Row = 3 And Target.Column = 6 And Target.Count = 1 Then Call ms_month

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 1 And Target.Count = 1 Then Call sku_week
If Target.Row = 5 And Target.Column = 4 Or Target.Column = 5 And Target.Count = 1 Then Call po_week
If Target.Row = 5 And Target.Column = 6 And Target.Count = 1 Then Call ms_week
End Sub



Sub sku_month()
' clears all other fields
If Cells(3, 1).Value = "" Then
Sheets("Weekflow").Cells(5, 1).ClearContents
Sheets("Lineflow").Cells(5, 1).ClearContents
Exit Sub
Else
End If
Application.EnableEvents = False
Cells(3, 6).FormulaR1C1 = _
        "=VLOOKUP(R3C1,'[Active Skus - Developments.xlsm]Active Sku List'!C1:C3,3,FALSE)"
Cells(3, 6).Copy
Cells(3, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
Sheets("Weekflow").Cells(5, 1).Value = Sheets("Monthflow").Cells(3, 1).Value
Sheets("Weekflow").Cells(5, 6).Value = Sheets("Monthflow").Cells(3, 6).Value
Sheets("Lineflow").Cells(5, 1).Value = Sheets("Monthflow").Cells(3, 1).Value
Sheets("Lineflow").Cells(5, 6).Value = Sheets("Monthflow").Cells(3, 6).Value
Application.EnableEvents = True
End Sub



Sub sku_week()
' clears all other fields
If Sheets("Weekflow").Cells(5, 1).Value = "" Then
Sheets("Monthflow").Cells(3, 1).ClearContents
Sheets("Lineflow").Cells(5, 1).ClearContents
Exit Sub
Else
End If
Application.EnableEvents = False
Cells(5, 6).FormulaR1C1 = _
        "=VLOOKUP(R5C1,'[Active Skus - Developments.xlsm]Active Sku List'!C1:C3,3,FALSE)"
Cells(5, 6).Copy
Cells(5, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
Sheets("Monthflow").Cells(3, 1).Value = Sheets("Weekflow").Cells(5, 1).Value
Sheets("Monthflow").Cells(3, 6).Value = Sheets("Weekflow").Cells(5, 6).Value
Sheets("Lineflow").Cells(5, 1).Value = Sheets("Monthflow").Cells(3, 1).Value
Sheets("Lineflow").Cells(5, 6).Value = Sheets("Monthflow").Cells(3, 6).Value
Application.EnableEvents = True
End Sub

I cannot post a workbook as it is too large for the allowance of the Forum

[Aflatoon]

You appear to be using a SelectionChange event as well - that is what is being triggered I suspect. Since it clears cells, it is probably triggering your other change events.

[Poundland]

You appear to be using a SelectionChange event as well - that is what is being triggered I suspect. Since it clears cells, it is probably triggering your other change events.

What type of Event Change should I be using? I what the event to trigger when a change is made not just by selecting the cell.

[Poundland]

You appear to be using a SelectionChange event as well - that is what is being triggered I suspect. Since it clears cells, it is probably triggering your other change events.

I just realized what you meant, and have changed the Event on the Weekflow sheet to a Change Event rather than a Selection Change Event, it now works a treat.
Sometimes you simply cannot see the wood for the trees..

Thanks for you help buddy..

[Aflatoon]

My pleasure.

[Paul_Hossler]

If there's a possibility that something in the event handler logic might trigger a further change, you should probably disable events until the tasks are done to avoid a runaway

Private Sub Worksheet_Change(ByVal Target As Range) 

    Application.EnableEvents =False

    If Target.Row = 3 And Target.Column = 1 And Target.Count = 1 Then Call sku_month 
    If Target.Row = 3 And Target.Column = 4 Or Target.Column = 5 And Target.Count = 1 Then Call po_month 
    If Target.Row = 3 And Target.Column = 6 And Target.Count = 1 Then Call ms_month 

    Application.EnableEvents = True
     
End Sub