Close form...

**pianoman** · 06-02-2006, 08:46 AM

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

Matt Lunn · 06-02-2006, 10:35 AM

"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** · 06-02-2006, 10:40 AM

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,

Originally Posted by Matt Lunn

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

Matt Lunn · 06-02-2006, 11:40 AM

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

Matt Lunn · 06-02-2006, 11:45 AM

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

**pianoman** · 06-05-2006, 04:58 AM

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

Chip Pearson · 06-05-2006, 08:25 AM

You can close an add-in with code like

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

"pianoman"
<pianoman.28x8q1_1149498011.2944@excelforum-nospam.com> wrote in
message
news:pianoman.28x8q1_1149498011.2944@excelforum-nospam.com...
>
> 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
>

**pianoman** · 06-05-2006, 09:09 AM

Hi Chip,
Thanks for that... it does close the form, exactly as required, and I'm assuming that running the same command as = True will re-install 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 further forward!

your thoughts?!

Thank you,

Gareth