Unhide worksheets with message box entry

[wpryan]

I have some code, the purpose of which is to unhide a group of worksheets based on the data entry of one or two cells (H11 and H12). I am trying to enforce this by way of a message box entry. The problem I am encountering is that the only place I can find that it works (more or less) is in the Worksheet_Change event, and the problem with this is that every other line of code that causes a change in the worksheet will invoke the message box. I only want it to appear based on a change in range H11. If i try to to put it in the Worksheet_SelectionChange then it crashes (hard - stack overflows and other errors, including simply shutting down). I'd appreciate any advice here, or maybe there's another way to do it... The terms UnhideOpen, UnhideTSC, UnhideRoutine and UnhideDemo refer to the various worksheet groups. The code is here:

Application.ScreenUpdating = False
 
Dim purpose As String
purpose = Range("H11").Value
Dim Answer As String
 
If purpose = "" Then
    UnhideOpen 
End If
 
If purpose = "Annual PM or TSC" Or purpose = "Product Clinical Demo" Or _
purpose = "Installation" Or purpose = "Incoming Inspection" Then
    UnhideTSC 
End If
 
If purpose = "Clinical Support" Or purpose = "Emergency Service" Or _
purpose = "Upgrade or Update" Then
    Answer = MsgBox("Will a TSC be performed during this service visit?", vbYesNo + vbQuestion, "Perform TSC?")
        If Answer = vbYes Then
            UnhideTSC
        Else
            UnhideRoutine
        End If
End If
 
If purpose = "Conference or Commercial Demo" Or purpose = "Decommission" Then
    UnhideDemo
End If
 
Application.EnableEvents = False

 

[EB08]

I havent looked too deeply into this, but just from looking quickly i would think that you could use a Worksheet_Change and start the entire procudure off with

if target.address="$H$11" then...

I would think at this point that the entire procedure would just be skipped if the change happened anywhere other than H11; thus getting rid of the issue of the unwanted msgbox.

 

[lenze]

If Intersect(Target, Range("H11:H12') Is Nothing Then Exit Sub
Application.EnableEvents = False
'rest of your code
Application.EnableEvents = True

lenze

 

[wpryan]

Thanks Lenze, that did the trick! This will be the second or third time you pulled my bacon out! Cheers!