| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
code in module A to not execute a Worksheet_SelectionChange sub of another module
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:="=" --------------------------------------------------------------------------------------------- |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
code in module A to not execute a Worksheet_SelectionChange subof another module
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 Jack Sons wrote: 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:="=" --------------------------------------------------------------------------------------------- |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
code in module A to not execute a Worksheet_SelectionChange sub of another module
Hi Jack,
Try: Sub Lijst_klassen_schoonh_B() Application.EnableEvents = False "your code Application.EnableEvents = True End Sub --- Regards, Norman "Jack Sons" wrote in message ... 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:="=" --------------------------------------------------------------------------------------------- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
code in module A to not execute a Worksheet_SelectionChange sub of another module
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 "Norman Jones" wrote in message ... Hi Jack, Try: Sub Lijst_klassen_schoonh_B() Application.EnableEvents = False "your code Application.EnableEvents = True End Sub --- Regards, Norman "Jack Sons" wrote in message ... 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:="=" --------------------------------------------------------------------------------------------- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
code in module A to not execute a Worksheet_SelectionChange sub of another module
Rowan and Norman,
You are both fast as lightning. Thanks a lot. Jack. "Norman Jones" schreef in bericht ... Hi Jack, Try: Sub Lijst_klassen_schoonh_B() Application.EnableEvents = False "your code Application.EnableEvents = True End Sub --- Regards, Norman "Jack Sons" wrote in message ... 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:="=" --------------------------------------------------------------------------------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
| Change case...help please | Excel Worksheet Functions | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
| Using other workbooks.. | Excel Worksheet Functions | |||
| Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Linear Mode
