layered vbyesno

AwesomeSteph

Board Regular
Joined
Aug 18, 2017
Messages
80
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

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,991
Office Version
365
Platform
Windows
Is this what you want
Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
Hi try


Code:
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
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top