Disable Workbook_SheetChange for specific worksheets

[LUgrad12]

Hello All,</SPAN>

I am very new to VBA so I would like to apologize in advance if this is a highly trivial and basic question.</SPAN>

With that said I am developing a workbook that will ultimately end up serving as a database. In the workbook, there will be ~120 tabs with each tab containing information for a single customer. In addition to the customer tabs, there are three tabs serving as the database itself. I am currently utilizing: Private Sub Workbook_SheetChange(ByVal SH As Object, ByVal Target As Range) given that I would like the ensuing macro to work on all 120 of the customer tabs. Is there a way to use the workbook event change sub yet teach the sub to ignore changes on specific sheets?</SPAN>

I currently have this:</SPAN>


Private Sub Workbook_SheetChange(ByVal SH As Object, ByVal Target As Range)

If Target.Address = "$F$14" Then
Select Case Range("F14")
Case "Yes"
MsgBox "Please enter ............."
Range("G14").Locked = False
Range("G14").FormulaHidden = False
Range("G14").Activate

Case Is <> "Other"
Range("G14").Locked = False
Range("G14").ClearContents
Range("G14").Locked = True
Range("G14").FormulaHidden = False

End Select


End If


and the macro continues on much futher from there. Is there way for me to tell the sub to not run if sheets named "Grid" , "Comparison Table" , and "Group" are active?

 

[Part Time user]

Hi, how about someting like this?

Private Sub Workbook_SheetChange(ByVal SH As Object, ByVal Target As Range)

Select case Target.Parent.Name
Case "Grid", "Comparison Table", "Group"
    Exit sub
Case else
    'Do nothing, let procedure continue.
End select

If Target.Address = "$F$14" Then
Select Case Range("F14")
Case "Yes"
MsgBox "Please enter ............."
Range("G14").Locked = False
Range("G14").FormulaHidden = False
Range("G14").Activate

Case Is <> "Other"
Range("G14").Locked = False
Range("G14").ClearContents
Range("G14").Locked = True
Range("G14").FormulaHidden = False

End Select


End If

 

[LUgrad12]

Thank you so much for the response. That works spectacularly. The only question that I have is if the workbook is saved and closed on the “grid” sheet, will the ensuing macro run if the workbook opens directly to the “Grid” sheet? My understanding of the select case sub is that it activates once a case is prompted to run. Given that the “Grid” sheet was opened directly and not explicitly selected, will the sub ignore that first command and run the other macros?</SPAN>

 

[Part Time user]

The Workbook_SheetChange event only fires when the value of any cell on any worksheet within the workbook changes, so the sub won't run at all just by opening the workbook.

If, however, you have a workbook_Open event defined that changes some cell value, then the sub will fire, and depending on the sheet holding the changed value, will exit or continue based on the cells parent. If that's the case, you'll probably have to do a little refactoring to get it to work right.

 

[Norie]

Use the Sh argument passed to the event.

Option Explicit

Private Sub Workbook_SheetChange(ByVal SH As Object, ByVal Target As Range)

    Select Case SH.Name
        Case "Grid", "Comparison Table", "Group"
            Exit Sub
        Case Else
            ' Do nothing
    End Select

    If Target.Address = "$F$14" Then

        Select Case Target.Value
            Case "Yes"
                MsgBox "Please enter ............."
                SH.Range("G14").Locked = False
                SH.Range("G14").FormulaHidden = False
                Application.Goto SH.Range("G14")

            Case Is <> "Other"
                SH.Range("G14").Locked = False
                SH.Range("G14").ClearContents
                SH.Range("G14").Locked = True
                SH.Range("G14").FormulaHidden = False

        End Select

    End If

End Sub

[/code]