Hi guys,
I have an issue with a form I'm using... I have a sub which activates when something changes on the sheet, and runs some code on the master sheet, then returns to the form. The trouble is that the changes it makes don't seem to have any lasting effect. i suspect it is because the form, with the old data still in it, is still open, and therefore, re-pastes the old data, once the 'change' macro has run. How do I close and re-open the form so that the macro can run, in-hindered? It's not a standard form, it's JWalk's form...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
Dim TargetRow As Integer
If Target.Cells.Count > 1 Then Exit Sub 'one cell at a time??

If Not Intersect(Target, Me.Range("R2:R130")) Is Nothing Then
With Worksheets("Yearly Snapshots")
If IsEmpty(.Range("A2").Value) = True Then
Set DestCell = .Range("a2")
Else
Set DestCell = .Range("a1").End(xlDown).Offset(1, 0)
End If
End With

Target.EntireRow.Copy _
Destination:=DestCell
Application.EnableEvents = False
With Worksheets("Master Sheet")
TargetRow = Target.Row
Range("R" & TargetRow & ":U" & TargetRow).ClearContents the line that makes the changes
Range("A1").Select
End With
MsgBox ("Now enter a new Annual Review Date")
'Application.Run "dataform2.xla!ShowDataForm"
End If
Application.EnableEvents = True
End Sub



Thanks everyone