Worksheet_Change

S

scrimmy

I have the following code in a worksheet in an EXCEL 2003 file(call it
File1). It seems to work fine if I only have one EXCEL file open. If I have
another EXCEL file open(File2), the code seems to want to execute in that
file also. How to I restrict the code to File1?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("PJMdata!A1:A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then



Range("PJMdata!J1:N1").Select
Selection.Copy
Range("PJMdata!Q2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("PJMdata!A1").Select



End If
End Sub
 
J

Jim Rech

I don't see any reason why your code should affect another workbook. Since
I cleaned it up in looking it over I thought I'd post my equivalent code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
With Application
.EnableEvents = False
Range("J1:N1").Copy
Range("Q2").End(xlDown).Offset(1).PasteSpecial
Paste:=xlPasteValues
Range("A1").Select
.EnableEvents = True
.CutCopyMode = False
End With
End If
End Sub

Removed some unnecessary stuff like selecting cells. I added
Application.EnableEvents to keep the code itself from firing a change event.

--
Jim
|I have the following code in a worksheet in an EXCEL 2003 file(call it
| File1). It seems to work fine if I only have one EXCEL file open. If I
have
| another EXCEL file open(File2), the code seems to want to execute in that
| file also. How to I restrict the code to File1?
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| Dim KeyCells As Range
|
| ' The variable KeyCells contains the cells that will
| ' cause an alert when they are changed.
| Set KeyCells = Range("PJMdata!A1:A1")
|
| If Not Application.Intersect(KeyCells, Range(Target.Address)) _
| Is Nothing Then
|
|
|
| Range("PJMdata!J1:N1").Select
| Selection.Copy
| Range("PJMdata!Q2").Select
| Selection.End(xlDown).Select
| ActiveCell.Offset(1, 0).Range("A1").Select
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
|
| Range("PJMdata!A1").Select
|
|
|
| End If
| End Sub
 
S

scrimmy

Jim,
Thanks for looking at this.
I tried using your code in my workbook and still have the problem of it
trying to execute in another workbook that I have open.
 
J

Jim Rech

still have the problem of it trying to execute in another workbook that I
Well, I' m sure not making this up but I have no idea how this can possibly
be happening (unless your other workbook is in fact another _window_ of the
same workbook. Nah<g>).

--
Jim
| Jim,
| Thanks for looking at this.
| I tried using your code in my workbook and still have the problem of it
| trying to execute in another workbook that I have open.
|
| "Jim Rech" wrote:
|
| > I don't see any reason why your code should affect another workbook.
Since
| > I cleaned it up in looking it over I thought I'd post my equivalent
code:
| >
| > Private Sub Worksheet_Change(ByVal Target As Range)
| > If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
| > With Application
| > .EnableEvents = False
| > Range("J1:N1").Copy
| > Range("Q2").End(xlDown).Offset(1).PasteSpecial
| > Paste:=xlPasteValues
| > Range("A1").Select
| > .EnableEvents = True
| > .CutCopyMode = False
| > End With
| > End If
| > End Sub
| >
| > Removed some unnecessary stuff like selecting cells. I added
| > Application.EnableEvents to keep the code itself from firing a change
event.
| >
| > --
| > Jim
| > | > |I have the following code in a worksheet in an EXCEL 2003 file(call it
| > | File1). It seems to work fine if I only have one EXCEL file open. If
I
| > have
| > | another EXCEL file open(File2), the code seems to want to execute in
that
| > | file also. How to I restrict the code to File1?
| > |
| > | Private Sub Worksheet_Change(ByVal Target As Range)
| > | Dim KeyCells As Range
| > |
| > | ' The variable KeyCells contains the cells that will
| > | ' cause an alert when they are changed.
| > | Set KeyCells = Range("PJMdata!A1:A1")
| > |
| > | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
| > | Is Nothing Then
| > |
| > |
| > |
| > | Range("PJMdata!J1:N1").Select
| > | Selection.Copy
| > | Range("PJMdata!Q2").Select
| > | Selection.End(xlDown).Select
| > | ActiveCell.Offset(1, 0).Range("A1").Select
| > | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| > | SkipBlanks _
| > | :=False, Transpose:=False
| > |
| > | Range("PJMdata!A1").Select
| > |
| > |
| > |
| > | End If
| > | End Sub
| >
| >
| >
 
J

Jim Rech

I'm sure YOU'RE not making

--
Jim
| >>still have the problem of it trying to execute in another workbook that
I
| >>have open.
|
| Well, I' m sure not making this up but I have no idea how this can
possibly
| be happening (unless your other workbook is in fact another _window_ of
the
| same workbook. Nah<g>).
|
| --
| Jim
| || Jim,
|| Thanks for looking at this.
|| I tried using your code in my workbook and still have the problem of it
|| trying to execute in another workbook that I have open.
||
|| "Jim Rech" wrote:
||
|| > I don't see any reason why your code should affect another workbook.
| Since
|| > I cleaned it up in looking it over I thought I'd post my equivalent
| code:
|| >
|| > Private Sub Worksheet_Change(ByVal Target As Range)
|| > If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
|| > With Application
|| > .EnableEvents = False
|| > Range("J1:N1").Copy
|| > Range("Q2").End(xlDown).Offset(1).PasteSpecial
|| > Paste:=xlPasteValues
|| > Range("A1").Select
|| > .EnableEvents = True
|| > .CutCopyMode = False
|| > End With
|| > End If
|| > End Sub
|| >
|| > Removed some unnecessary stuff like selecting cells. I added
|| > Application.EnableEvents to keep the code itself from firing a change
| event.
|| >
|| > --
|| > Jim
|| > || > |I have the following code in a worksheet in an EXCEL 2003 file(call it
|| > | File1). It seems to work fine if I only have one EXCEL file open.
If
| I
|| > have
|| > | another EXCEL file open(File2), the code seems to want to execute in
| that
|| > | file also. How to I restrict the code to File1?
|| > |
|| > | Private Sub Worksheet_Change(ByVal Target As Range)
|| > | Dim KeyCells As Range
|| > |
|| > | ' The variable KeyCells contains the cells that will
|| > | ' cause an alert when they are changed.
|| > | Set KeyCells = Range("PJMdata!A1:A1")
|| > |
|| > | If Not Application.Intersect(KeyCells, Range(Target.Address)) _
|| > | Is Nothing Then
|| > |
|| > |
|| > |
|| > | Range("PJMdata!J1:N1").Select
|| > | Selection.Copy
|| > | Range("PJMdata!Q2").Select
|| > | Selection.End(xlDown).Select
|| > | ActiveCell.Offset(1, 0).Range("A1").Select
|| > | Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
|| > | SkipBlanks _
|| > | :=False, Transpose:=False
|| > |
|| > | Range("PJMdata!A1").Select
|| > |
|| > |
|| > |
|| > | End If
|| > | End Sub
|| >
|| >
|| >
|
|
 
S

scrimmy

Thanks again for looking at this. The other workbook is NOT another window
of the same workbook. Could the problem be caused by the web query that I am
using to refresh the data in cell A1? I have the web query set up to run
every 5 minutes and if the value it returns to cell A1 has changed, the code
will execute. As I've said, it works fine unless I am working in another
workbook.
 
J

Jim Rech

I don't do many web queries so I don't know if it might be causing this.

If you have another workbook open and manually make an entry in A1 does the
problem occur?
 
S

scrimmy

I believe have found a way around my problem. It seems that if I want to
work with other EXCEL files while the file in question is open, I have to
physically open a separate session of EXCEL. This is a bit of nuisance and
doesn't answer the question of why the code tries to run in other files, but
it gets me past my problem. Thank you again for looking into this. Your
help is greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet Change Event 10
Macro 0
Simple colour dropdown 3
Macro to copy/paste from one workbook to another 8
Macro to run on hidden sheet 4
output date modified if data is entered for multiple rows 4
I almost have it - excel hidden - see message 2
Pasting a cell into bookmarked table from excel vba 1

Top