Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
OK I'm not sure what to do about this but I have some code running in the
Worksheet_Change(ByVal Target As Range) EVENT. This is just one of the examples but when a user enters a space " " in a cell the code inputs the default value, the code also formats values that are manually entered. The problem I'm having is that I may change the "Target.Value" several times and every time I change it the "Worksheet_Change" EVENT is fired again. When I input a space for the default address the Worksheet_Change EVENT is fired 500 times. How do I get around this? Since the code is a few Nested IF statements the code is jumping all over the place. The only thing I'm thinking of is moving the Target.value into a variable first and then start working with it. Would this work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
Use Application.EnableEvents to temporarily turn off events.
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' your code here Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "HotRod" wrote in message ... OK I'm not sure what to do about this but I have some code running in the Worksheet_Change(ByVal Target As Range) EVENT. This is just one of the examples but when a user enters a space " " in a cell the code inputs the default value, the code also formats values that are manually entered. The problem I'm having is that I may change the "Target.Value" several times and every time I change it the "Worksheet_Change" EVENT is fired again. When I input a space for the default address the Worksheet_Change EVENT is fired 500 times. How do I get around this? Since the code is a few Nested IF statements the code is jumping all over the place. The only thing I'm thinking of is moving the Target.value into a variable first and then start working with it. Would this work? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
Add ...
Application.EnableEvents = False .... at the start and ... Application.EnableEvents = True .... at the end. This will stop the Worksheet_Change event firing when your macro 'changes' a cells value. If you don't understand how / why, just ask & i'll explain further. Regards - Steve. "HotRod" wrote in message ... OK I'm not sure what to do about this but I have some code running in the Worksheet_Change(ByVal Target As Range) EVENT. This is just one of the examples but when a user enters a space " " in a cell the code inputs the default value, the code also formats values that are manually entered. The problem I'm having is that I may change the "Target.Value" several times and every time I change it the "Worksheet_Change" EVENT is fired again. When I input a space for the default address the Worksheet_Change EVENT is fired 500 times. How do I get around this? Since the code is a few Nested IF statements the code is jumping all over the place. The only thing I'm thinking of is moving the Target.value into a variable first and then start working with it. Would this work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
The code described in the answers will do exactly what you want. Just one
thing I would like to add. Anytime you play with the application level settings it is a good idea to use an error handler to set everything straight if an error crashes your code. My preference is for something more like this... Private Sub Worksheet_Change(ByVal Target As Range) on error goto ErrorHandler Application.EnableEvents = False ' your code here ErrorHandler: Application.EnableEvents = True End Sub If your code crashes then the error handler is invoked and the events are back on. Careful halting code while debugging because once again you will set the events off without turning them back on and strange things will start to happen when you run more code... HTH "HotRod" wrote: OK I'm not sure what to do about this but I have some code running in the Worksheet_Change(ByVal Target As Range) EVENT. This is just one of the examples but when a user enters a space " " in a cell the code inputs the default value, the code also formats values that are manually entered. The problem I'm having is that I may change the "Target.Value" several times and every time I change it the "Worksheet_Change" EVENT is fired again. When I input a space for the default address the Worksheet_Change EVENT is fired 500 times. How do I get around this? Since the code is a few Nested IF statements the code is jumping all over the place. The only thing I'm thinking of is moving the Target.value into a variable first and then start working with it. Would this work? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
OK. I've tried to do a four column SORT with the theory that excel uses a
persistent sort but it doesn't seem to work. What I did was actually sort by the Quarter first and then by the Actual Date. (I actually added some false dates to Quarter 4 to see if it would rise to the top and prove that the code was not working) This is what I used below. Any ideas on how to make excel keep the sort persistent? Do I need to highlight the range first? Debug.Print My_Column_Sort(First_Row, True, "A") Debug.Print My_Column_Sort(First_Row, True, "E", "B", "C") Function My_Column_Sort(First_Data_Row As Integer, AscendingOrder As Boolean, _ First_Column As String, Optional Second_Column As String = "Z", _ Optional Third_Column As String = "Z") Range("A" & First_Data_Row & ":AA55550").Sort Key1:=Range(First_Column & First_Data_Row), _ Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row), _ Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _ Order3:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to prevent Worksheet_Change loop?
Even with the Cells Selected I was not able to do a persistent sort in VBA
code, at least not using the code I posted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW CAN I PREVENT EXCEL FILES FROM BEING DELETED OR PREVENT TRASH | Excel Discussion (Misc queries) | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |