Poundland
12-01-2015, 04:33 AM
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 :(
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 :(