Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event | Excel Discussion (Misc queries) | |||
Change of Row event | Excel Discussion (Misc queries) | |||
Chart Values that change based on Change event | Excel Programming | |||
Change minimumscale with the change event of a combobox | Excel Programming | |||
change event/after update event?? | Excel Programming |