code in module A to not execute a Worksheet_SelectionChange sub of another module

[Jack Sons]

Hi all,

In the sheet module of sheet("totaal") I have a sub (see below between the
first two ---- lines) that gives me a certain button in the upper left
corner of sheet("totaal") when I scroll to the right to another part of that
large sheet.

In a regular code module I have a sub (see below the second --- line) that
switches to the Worksheet_SelectionChange sub when it arrives at the
autofilter part and in numerous other occasions of which the code lines are
not shown below. In principle it does no harm, but it takes a lot of time,
which is unnecessary.

How can I tell the second sub (the Lijst_klassen_schoonh_B sub) to ignore
the first (the Worksheet_SelectionChange sub)?

Jack Sons
The Netherlands

---------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
If ActiveCell.Column > 16 Then
Dim myShape As Shape
Set myShape = Me.Shapes("historie")
With Me.Cells(1, ActiveWindow.ScrollColumn)
myShape.Top = 30 '.Top
myShape.Left = .Left
End With
End If
End Sub
------------------------------------------------------------------------------

Sub Lijst_klassen_schoonh_B()
Application.ScreenUpdating = False
Sheets("totaal").Select
'Calculate
kolkol = ActiveCell.Column
rijrij = ActiveCell.Row

Sheets("SH00").Visible = True
Sheets("SN00").Visible = True
Sheets(Array("SH00", "SN00")).Select
Sheets("SH00").Activate
Cells.Select
Selection.ClearContents
With Selection.Font
.Name = "Times New Roman"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlNone
End With
Selection.Font.Bold = False

Selection.Borders(xlLeft).LineStyle = xlNone
Selection.Borders(xlRight).LineStyle = xlNone
Selection.Borders(xlTop).LineStyle = xlNone
Selection.Borders(xlBottom).LineStyle = xlNone
Selection.BorderAround LineStyle:=xlNone

Selection.RowHeight = 12
Range("A1").Select

Sheets("totaal").Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=18, Criteria1:="SHB???04???", _
Operator:=xlAnd
Selection.AutoFilter Field:=51, Criteria1:="="
Selection.AutoFilter Field:=52, Criteria1:="="
---------------------------------------------------------------------------------------------

 

[Rowan Drummond]

Use the Application.EnableEvents commands to stop the
Worksheet_Selection change event firing while the second sub is running eg:

Sub Lijst_klassen_schoonh_B()

On Error GoTo Error_Handler

Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("totaal").Select
'Calculate
kolkol = ActiveCell.Column
rijrij = ActiveCell.Row
'etc


Error_Handler:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Hope this helps
Rowan

 

[Norman Jones]

Hi Jack,

Try:

Sub Lijst_klassen_schoonh_B()

Application.EnableEvents = False

"your code


Application.EnableEvents = True
End Sub

 

[Norman Jones]

Hi Jack,

Just to add, if events are disabled, it is wise to re-enable them in an
error handler at the end of your code, as suggested by Rowan.


---
Regards,
Norman

Hi Jack,

Try:

Sub Lijst_klassen_schoonh_B()

Application.EnableEvents = False

"your code


Application.EnableEvents = True
End Sub

 

[Jack Sons]

Rowan and Norman,

You are both fast as lightning. Thanks a lot.

Jack.

Hi Jack,

Try:

Sub Lijst_klassen_schoonh_B()

Application.EnableEvents = False

"your code


Application.EnableEvents = True
End Sub