Macro to trigger upon Worksheet Deactivate, instead of Worksheet Change

[scott_od]

The code below uses the value in cells G2:G6 (worksheet name "Data"), whenever they are updated, to populate the data validation input message in worksheet "Overview" cells F4:F8
And the value in cells I2:I6 (worksheet name "Data"), to populate the data validation input message in worksheet "Overview" cells G4:G8

However, I would like to change the code so that it's triggered only when the user leaves the “Data” worksheet, by converting the code to a Worksheet Deactivate event.
Would appreciate anybody's help. Thank you.

"Data" Worksheet

"Overview" Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.CountLarge = 1 And Not Intersect(Target, Range("G2:G6")) Is Nothing Then
        With Worksheets("Overview").Range("F" & Target.Row + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Comment"
            .InputMessage = Target.Value
        End With
   
    ElseIf Target.CountLarge = 1 And Not Intersect(Target, Range("I2:I6")) Is Nothing Then
        With Worksheets("Overview").Range("G" & Target.Row + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Original Target Date"
            .InputMessage = Target.Value
        End With
    End If
   
End Sub

 

[offthelip]

try this:

Private Sub Worksheet_Deactivate()
    For Targetrow = 2 To 6
      TargetValue = Worksheets("Data").Range("G" & Targetrow)
        With Worksheets("Overview").Range("F" & Targetrow + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Comment"
            .InputMessage = TargetValue
        End With
   
      TargetValue = Worksheets("Data").Range("I" & Targetrow)
        With Worksheets("Overview").Range("G" & Targetrow + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Original Target Date"
            .InputMessage = TargetValue
        End With
    Next Targetrow

End Sub

 

[scott_od]

thank you, this works perfectly

 

[scott_od]

try this:

Private Sub Worksheet_Deactivate()
    For Targetrow = 2 To 6
      TargetValue = Worksheets("Data").Range("G" & Targetrow)
        With Worksheets("Overview").Range("F" & Targetrow + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Comment"
            .InputMessage = TargetValue
        End With
  
      TargetValue = Worksheets("Data").Range("I" & Targetrow)
        With Worksheets("Overview").Range("G" & Targetrow + 2).Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
            .InputTitle = "Original Target Date"
            .InputMessage = TargetValue
        End With
    Next Targetrow

End Sub

One small bug that I have come across:

The "project overview" header is a shape, and if the shape is selected previously (before switching from the "Data" worksheet to the "Overview" worksheet), then I get runtime error 1004 'Application-defined or object defined error'

 

[offthelip]

I have no idea why that is happening, since this is unrelated to your original problem I suggest you start a new thread, one of the other Excel experts can hopefully help you