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


--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=547814