Omit vba script from running in specific sheets.

[omairhe]

Hey Excel Gurus,

I have a code which runs in every sheet. However I would like to bypass this code from two specific worksheets, namely "HSheet" and "Summary"

The code is as following.

Private Sub Workbook_Sheetactivate(ByVal Sh As Object)
If Worksheets("HSheet").Range("B2") <> Selection.Column And Worksheets("HSheet").Range("B3") > 21 Then
   Dim lCol As Long
    Application.EnableEvents = 0
    Application.ScreenUpdating = 0
    With Sheets("HSheet")
         lCol = .[B3]
    End With

            ActiveSheet.Cells(ActiveCell.Row, lCol).Select
            ActiveWindow.ScrollColumn = Selection.Column
    Application.EnableEvents = 1
    Application.ScreenUpdating = 1
End If
End Sub

Thanks and will appreciate...

 

[dmt32]

hi,
try

Private Sub Workbook_Sheetactivate(ByVal Sh As Object)

If Not IsError(Application.Match(Sh.Name, Array("HSheet", "Summary"), 0)) Then Exit Sub

'rest of code
End Sub

Dave

 

[omairhe]

It will work on the above code I posted.
But on the following code it is not working. How to achieve the same with the following code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not IsError(Application.Match(Sh.Name, Array("HSheet", "Summary"), 0)) Then Exit Sub
If Worksheets("HSheet").Range("B2") > 21 Then
ActiveWindow.ScrollRow = Worksheets("HSheet").Range("B1")
ActiveWindow.ScrollColumn = Selection.Column
End If
If Worksheets("HSheet").Range("B3") <> Selection.Column And Worksheets("HSheet").Range("B2") > 21 Then
    Dim ws As Worksheet, lCol As Long
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    With Sheets("HSheet")
        .[B3] = .[B2]
         lCol = .[B2]
    End With
        For Each ws In Sheets
            ws.Activate
            ws.Cells(ActiveCell.Row, lCol).Select
              ActiveWindow.ScrollColumn = Selection.Column
        Next
        Sh.Activate
    Application.EnableEvents = 1
    Application.ScreenUpdating = 1
End If
End Sub

???

 

[omairhe]

Thanks but adding the following line fixed it. Seen some code in google and thought I give it a try. Hehe

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Worksheets("HSheet").Range("B2") > 21 Then
ActiveWindow.ScrollRow = Worksheets("HSheet").Range("B1")
ActiveWindow.ScrollColumn = Selection.Column
End If
If Worksheets("HSheet").Range("B3") <> Selection.Column And Worksheets("HSheet").Range("B2") > 21 Then
    Dim ws As Worksheet, lCol As Long
    Application.ScreenUpdating = 0
    Application.EnableEvents = 0
    With Sheets("HSheet")
        .[B3] = .[B2]
         lCol = .[B2]
    End With
        For Each ws In Sheets
                                                                    If ws.Name <> "HSheet" And ws.Name <> "Summary" Then
                ws.Activate
                ws.Cells(ActiveCell.Row, lCol).Select
                ActiveWindow.ScrollColumn = Selection.Column
            End If
        Next
        Sh.Activate
    Application.EnableEvents = 1
    Application.ScreenUpdating = 1
End If
End Sub

 

[omairhe]

Now both the codes are omitting the Vba script.
Thank you.

 

[dmt32]

Now both the codes are omitting the Vba script.
Thank you.

Tested code & worked OK for me and not sure why you had an issue but if managed to resolve another way all well & good


Dave