Macro Problem
 

I have a code that creates a quote by moving sheets to a new file. When I
step through the macro, for some reason it is calling upon a
worksheet_selectionchange and causing my virtual memory to go through the
roof. How do I prevent my macro from automatically calling about the
worksheet_selectionchange that I have created. Here is the code that is
causing it to goto the worksheet_selectionchange, along with the code for the
Worksheet_selectionchange. My worksheet_selectionchange is under my
"Agreement" Tab and that is when it gets called upon by the macro. Hope this
makes sense. Any help is appreciated.

AWB.Activate
Sheets("Agreement").Select
Sheets("Agreement").Copy Befo=Workbooks("Email Template
Macro3l.xls").Sheets _
(3)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 52 Then
With Worksheets("Agreement")
promptdialog.Show
End With
End If
End Sub

Macro Problem
 

Brad

You can use Application.EnableEvents = False at the start of your sub and
then set it to True at the end. That will prevent the selectionchange event
from firing when that particular code is run.

What you might consider, though, is revising your code so that nothing is
selected.

ActiveWorkbook.Sheets("Agreement").Copy Befo=etc...
With Activesheet
.Cells.Copy
.Range("A1").PasteSpecial etc..
End With
Application.CutCopyMode = False

By not selecting in your code, the event won't fire.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Brad wrote:
I have a code that creates a quote by moving sheets to a new file.
When I step through the macro, for some reason it is calling upon a
worksheet_selectionchange and causing my virtual memory to go through
the roof. How do I prevent my macro from automatically calling about
the worksheet_selectionchange that I have created. Here is the code
that is causing it to goto the worksheet_selectionchange, along with
the code for the Worksheet_selectionchange. My
worksheet_selectionchange is under my "Agreement" Tab and that is
when it gets called upon by the macro. Hope this makes sense. Any
help is appreciated.

AWB.Activate
Sheets("Agreement").Select
Sheets("Agreement").Copy Befo=Workbooks("Email Template
Macro3l.xls").Sheets _
(3)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 52 Then
With Worksheets("Agreement")
promptdialog.Show
End With
End If
End Sub

Macro Problem
 

Dick, thanks for the help, this has solved my problem. My next goal is to
clean up my code. Thanks again!!!!

"Dick Kusleika" wrote:

Brad

You can use Application.EnableEvents = False at the start of your sub and
then set it to True at the end. That will prevent the selectionchange event
from firing when that particular code is run.

What you might consider, though, is revising your code so that nothing is
selected.

ActiveWorkbook.Sheets("Agreement").Copy Befo=etc...
With Activesheet
.Cells.Copy
.Range("A1").PasteSpecial etc..
End With
Application.CutCopyMode = False

By not selecting in your code, the event won't fire.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Brad wrote:
I have a code that creates a quote by moving sheets to a new file.
When I step through the macro, for some reason it is calling upon a
worksheet_selectionchange and causing my virtual memory to go through
the roof. How do I prevent my macro from automatically calling about
the worksheet_selectionchange that I have created. Here is the code
that is causing it to goto the worksheet_selectionchange, along with
the code for the Worksheet_selectionchange. My
worksheet_selectionchange is under my "Agreement" Tab and that is
when it gets called upon by the macro. Hope this makes sense. Any
help is appreciated.

AWB.Activate
Sheets("Agreement").Select
Sheets("Agreement").Copy Befo=Workbooks("Email Template
Macro3l.xls").Sheets _
(3)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 52 Then
With Worksheets("Agreement")
promptdialog.Show
End With
End If
End Sub