Close form...

[pianoman]

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

 

[Guest]

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

"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]

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,

 

[Guest]

Hi,

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

Cheers

 

[pianoman]

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

 

[Chip Pearson]

You can close an add-in with code like

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


"pianoman"
message
news[email protected]...

 

[pianoman]

Hi Chip,
Thanks for that... it does close the form, exactly as required, an
I'm assuming that running the same command as = True will re-instal
the add-in afterwards.

However, it also seems to halt the code dead as well. the form closes
and then the code stops running on the same line, so I'm no furthe
forward!

your thoughts?!

Thank you,

Garet