layered vbyesno

[AwesomeSteph]

I've been struggling with trying to have a msgbox that is prompting other msgbox's. and a yes equaling two separate msgbox's.

if it were to look like a tree I would draw it to look like this:

New Yes/No;
____no=endsub
____1yes= New XXX Yes/no;
________________no=XXX (sheet not visible)
________________yes=XXX (sheet visible)
____2yes= New YYY Yes/No;
________________no=YYY (sheet not visible)
________________yes=YYY (sheet visible)

I've tried a few variations of the code I am working with but resulting with the second vbyesno not coming up at all.

Sheet14 is XXX
Sheet7 is YYY

Dim MsgTitle, MsgPrompt As String, Ret As Integer
    If Not Intersect(Target, Range("B12")) Is Nothing Then
    Application.ScreenUpdating = False
    MsgPrompt = "Is this New?"
    MsgTitle = "Possible Review Required"
    If Sheet1.[B12].Value <> "" Then
        Ret = MsgBox(MsgPrompt, vbYesNo, MsgTitle)
            If Ret = vbNo Then
            Sheet14.Visible = False
            Sheet7.Visible = False
        Else
            Ret = MsgBox("Is an XXX review required?", vbYesNo, "Possible XXX Review")
                If Ret = vbYes Then
                Sheet14.Visible = True
                Ret = MsgBox("Is a YYY review required?", vbYesNo, "Possible YYY Review")
                    If Ret = vbNo Then
                    Sheet7.Visible = False
                Else
                    Sheet7.Visible = True
                    End If
            Else
                Sheet14.Visible = False
End If
End If
End If
End If

 

[Fluff]

Is this what you want

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim MsgTitle, MsgPrompt As String, Ret As Integer
   If Not Intersect(Target, Range("B12")) Is Nothing Then
      Application.ScreenUpdating = False
      MsgPrompt = "Is this New?"
      MsgTitle = "Possible Review Required"
      If Target.Value <> "" Then
         If MsgBox(MsgPrompt, vbYesNo, MsgTitle) = vbNo Then
            Sheet14.Visible = False
            Sheet7.Visible = False
         Else
            If MsgBox("Is an XXX review required?", vbYesNo, "Possible XXX Review") = vbYes Then
               Sheet14.Visible = True
            Else
               Sheet14.Visible = False
            End If
            If MsgBox("Is a YYY review required?", vbYesNo, "Possible YYY Review") = vbYes Then
               Sheet7.Visible = True
            Else
               Sheet7.Visible = False
            End If
         End If
      End If
   End If
End Sub

 

[dmt32]

Hi try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MsgTitle As String, MsgPrompt As String
    Dim Ret As VbMsgBoxResult
    
    MsgPrompt = "Is this New?"
    MsgTitle = "Possible Review Required"
    
    If Not Intersect(Target, Me.Range("B12")) Is Nothing Then
    
    If Len(Target.Value) > 0 Then
        
        Sheet14.Visible = xlSheetVeryHidden
        Sheet7.Visible = xlSheetVeryHidden


            Ret = MsgBox(MsgPrompt, vbYesNo, MsgTitle)
            
            If Ret = vbYes Then
                Ret = MsgBox("Is an XXX review required?", vbYesNo, "Possible XXX Review")
                If Ret = vbYes Then Sheet14.Visible = xlSheetVisible
                Ret = MsgBox("Is a YYY review required?", vbYesNo, "Possible YYY Review")
                If Ret = vbYes Then Sheet7.Visible = xlSheetVisible
            End If
            
        End If
    End If
End Sub

Dave

 

[AwesomeSteph]

Thank you so much!