Change Event that Loops
 

I have code which triggers off a Change Event in a Sheet. When I chang
a cell in the spreadsheet, the code copies and pastes a range withi
this sheet to another range. The problem is that once the copy/past
engages, the code continues be called and loops on continuously. Ho
do I trigger a copy/paste using the Change Event without looping? I
my only option to copy/paste to a new sheet?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer


Export = Range("DropDownExport")

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")


End Select


End Su

--
Message posted from http://www.ExcelForum.com

Change Event that Loops
 

I think what is happening is that I need to restrict the change event t
the specific cell that I want to see the change in. The way the cod
is written, it triggers based on a change anywhere in the spreadsheet.
The change that needs to be tracked is
Range("DropDownExport")

--
Message posted from http://www.ExcelForum.com

Change Event that Loops
 

I think I just answered my own question. This code below seems to work



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer
Dim VRange As Range


Export = Range("DropDownExport")

Set VRange = Range("DropDownExport")
If Union(Target, VRange).Address = VRange.Address Then

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")

End Select

End If


End Su

--
Message posted from http://www.ExcelForum.com

Change Event that Loops
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer

Application.EnableEvents = False
On Error GoTo ws_exit
Export = Range("DropDownExport")

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")

End Select

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMonkey " wrote in message
...
I have code which triggers off a Change Event in a Sheet. When I change
a cell in the spreadsheet, the code copies and pastes a range within
this sheet to another range. The problem is that once the copy/paste
engages, the code continues be called and loops on continuously. How
do I trigger a copy/paste using the Change Event without looping? Is
my only option to copy/paste to a new sheet?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer


Export = Range("DropDownExport")

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")


End Select


End Sub


---
Message posted from http://www.ExcelForum.com/

Change Event that Loops
 

Another question. I have put a line just under the variabl
declarations which seems to stall the code:

Range("DynamicNameRange").ClearContents

I can't figure out why this does not work. When I use this line o
code by itself in a stand-alone module it works fine.




Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer
Dim VRange As Range

Range("DynamicNameRange").ClearContents

Export = Range("DropDownExport")

Set VRange = Range("DropDownExport")
If Union(Target, VRange).Address = VRange.Address Then

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")


End Select

End If


End Su

--
Message posted from http://www.ExcelForum.com

Change Event that Loops
 

Monkey,

That looks right. FYI, you can also disable and enable events to prevent
the looping. For instance:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer

Application.EnableEvents = False 'turns off events so no looping

Export = Range("DropDownExport")
Select Case Export
Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")
End Select

Application.EnableEvents = True

End Sub

hth,

Doug Glancy
"ExcelMonkey " wrote in message
...
I think I just answered my own question. This code below seems to work.



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer
Dim VRange As Range


Export = Range("DropDownExport")

Set VRange = Range("DropDownExport")
If Union(Target, VRange).Address = VRange.Address Then

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")

End Select

End If


End Sub


---
Message posted from http://www.ExcelForum.com/

Change Event that Loops
 

Monkey,

Looks like you've put yourself back into a loop. Clearing contents would be
a change, I think. So, either put the clearcontents inside your if
statement, or set enableevents to false.

hth,

Doug Glancy

"ExcelMonkey " wrote in message
...
Another question. I have put a line just under the variable
declarations which seems to stall the code:

Range("DynamicNameRange").ClearContents

I can't figure out why this does not work. When I use this line of
code by itself in a stand-alone module it works fine.




Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Export As Integer
Dim VRange As Range

Range("DynamicNameRange").ClearContents

Export = Range("DropDownExport")

Set VRange = Range("DropDownExport")
If Union(Target, VRange).Address = VRange.Address Then

Select Case Export

Case Is = 1
Range("FuelTypeName").Copy Destination:=Range("NamePaste")


End Select

End If


End Sub


---
Message posted from http://www.ExcelForum.com/