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

"pianoman" wrote:


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



"The trouble is that the changes it makes don't seem to have any lasting
effect. "

Hi,

Can you post a little more detail, plz. What type of control is not updating
and how does it link to your data on the sheet?

Hi Matt,
It's the "dataform2.xla" that I want to shut down, and then start u
again.

The form has links to fields on the "master sheet", so when I chang
data in the sheet directly, using a sub, whatever data is left in th
form seems to paste itself back into the Master sheet as soon as
return to the form. Maybe that's not what is actually happening, bu
that's the effect it's having.

Thank you,

Matt Lunn Wrote:
"pianoman" wrote:


Hi guys,
I have an issue with a form I'm using... I have a sub whic
activates
when something changes on the sheet, and runs some code on th
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 i
because
the form, with the old data still in it, is still open, an
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



"The trouble is that the changes it makes don't seem to have an
lasting
effect. "

Hi,

Can you post a little more detail, plz. What type of control is no
updating
and how does it link to your data on the sheet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54781

Hi,

Can you post the code in 'Application.Run "dataform2.xla!ShowDataForm"?

Cheers

"pianoman" wrote:


Hi Matt,
It's the "dataform2.xla" that I want to shut down, and then start up
again.

The form has links to fields on the "master sheet", so when I change
data in the sheet directly, using a sub, whatever data is left in the
form seems to paste itself back into the Master sheet as soon as I
return to the form. Maybe that's not what is actually happening, but
that's the effect it's having.

Thank you,

Matt Lunn Wrote:
"pianoman" wrote:


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



"The trouble is that the changes it makes don't seem to have any
lasting
effect. "

Hi,

Can you post a little more detail, plz. What type of control is not
updating
and how does it link to your data on the sheet?


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

Hi,
I don't have the source code for the form itself... it's Jwalk'
code, which I don't have. Is there not just a simple way of jus
closing an xla application that is running?

Thank you,

Garet

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54781

You can close an add-in with code like

Application.AddIns("Analysis ToolPak").Installed = False


"pianoman"
wrote in
message
...

Hi,
I don't have the source code for the form itself... it's
Jwalk's
code, which I don't have. Is there not just a simple way of
just
closing an xla application that is running?

Thank you,

Gareth


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