How to prevent Worksheet_Change loop?

[HotRod]

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?

 

[Chip Pearson]

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

 

[Stevie_mac]

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.

 

[Guest]

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]

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

 

[HotRod]

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