Skip ... Selection_Change Event

[Guest]

Hi all your assistance please ...,

I have a Selection_Change event in a particular wks. I also have several
Macros for expanding/hiding rows and columns in the same wks. How can I
'SKIP' the Selection_Change event when I trigger one of these macros but
have the Selection_Change event work correctly if the Macro is NOT triggered.

Many thanks in advance/sgl

 

[Tyla]

In general, what yuo'd want to do si set up a global variable which
flags that the macro was called and set that within the macro code
e.g. gbCalledFromMacro = True). Now, in the code behind your
Slectoin_Change() event, add code like the following:

Selectcion_change()

If (gbCalledFromMacro = True) Then
gbCalledFromMacro = False
Exit sub
End if'

' implied else
' Your normal goes code

End sub

HTH / Tyla /

 

[Guest]

Sgl,

You usually don't have to select cells when working with them in code. But
if you have to do the select, one way to avoid firing the SelectionChange
event is to disable/enable events:

Application.EnableEvents = False
Range("A4").Select
Application.EnableEvents = True

Another way is to declare a module level variable. For example:

Dim bFireSelect As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If bFireSelect Then Exit Sub

'your code here
End Sub
Sub testSelect()
bFireSelect = True
Range("A3").Select
bFireSelect = False
End Sub

 

[JE McGimpsey]

The best way to skip the Selection_Change event is to avoid making
selections in your macros. They're almost never necessary. Code that
works with range objects directly is almost always faster, smaller and
easier to maintain months down the road. If you're not sure how to do
that, post a reply with the relevant code.

Another way to do it is to wrap the guts of your macros with:

Public Sub foo()
On Error GoTo ErrorHandler
Application.EnableEvents = False

'your code here

ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

Disable events in your code, do the work, then turn on that even handling:

Application.enableevents = false
'your code to add/delete/hide rows or columns
application.enableevents = true

 

[Guest]

You are all fantastic. Never had so many positive replies so fast. All of
them worked.Thank you all/sgl