Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

**KK1234** · 01-19-2014, 05:17 PM

Hi all,
I want to incorporate message box at the beginning of a long code.

Message box has 'YES' and 'NO'

If answer is yes then it should continue with further routine.

If answer is NO then :
1. It should get message box ' Go to sheet2'
when pressed OK then sub is exited
2. and sheet2 activates

This is repeated until the answer is YES.
Code

Sub Msgbox_Yes_No()
      Dim Response As Integer
      Response = MsgBox(prompt:="Have you done Defib Checks First?'.", Buttons:=vbYesNo)
     If Response = vbYes Then
      MsgBox " Thanks"
      
      Else
      Do
       ' I need help here 
MsgBox " Go to Sheet2"
This workbook.Sheets ("Sheet2").activate
This workbook.Sheets ("Sheet2").visible     

 Loop While Answer = vbYes
      
End If


   End Sub

Any help please
Thanks

kind regards

**Norie** · 01-19-2014, 05:21 PM

You don't need the loop.

Sub Msgbox_Yes_No()
Dim Response As Integer

    Response = MsgBox(prompt:="Have you done Defib Checks First?'.", Buttons:=vbYesNo)
    
    If Response = vbYes Then
        MsgBox " Thanks"
        Exit Sub
    Else
        ' I need help here
        MsgBox "Go to Sheet2"
        ThisWorkbook.Sheets("Sheet2").Visible = True
        ThisWorkbook.Sheets("Sheet2").Activate

    End If

End Sub

**KK1234** · 01-19-2014, 05:43 PM

Hi Norie,
Perfect solution.
Thanks for your prompt response and Kind help.
It goes to sheet2 verywell if answer is NO
However since messagebox routine is part of a big code, it still triggers the following which is the next action in the big code.
How to stop it please?

  Call Msgbox_Yes_No
      
Do
    eADDR = Application.InputBox("Enter email address", _

I have clicked* to thank you. I will close this as solved subsequently.
Kind regards

**Norie** · 01-19-2014, 05:54 PM

If you click No in the mesage box the sub Msgbox_Yes_No will be exited, that's what this line does.

Exit Sub

If you want to totally stop code execution I would suggest putting the code for Msgbox_Yes_No inside the main sub.

**KK1234** · 01-19-2014, 05:59 PM

Hi Norie,
Thanks again.
It was a great help from you.
A learning curve for me.
Thanks again.

Kind regards

Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

Re: Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

Re: Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

Re: Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

Re: Message box if Yes continue with subroutine and if NO then exit sub and open sheet2

Thread Information

Users Browsing this Thread

Similar Threads

Write a subroutine in a module to change case upon exit of a cell to upper case

[SOLVED] Display Pop Up Message while a Subroutine Executes and close when Exit Sub

Exit Private Subroutine

all objects and userforms opened in a subroutine closed upon exit by default?

How do I exit a macro subroutine?

Bookmarks

Bookmarks

Posting Permissions