VBA MsgBox Yes/No

Question

When I select No in my message box prompt, the document still closes. I want it to stay open when i select no. I'm not sure how to do this. Thanks.

Option Explicit

Sub b()

 'declares docX as a document and stores it
  Dim docX As Documents
 'opens the document file
  Application.Documents.Open FileName:="C:\Users\johnr\OneDrive\Documents\CIS 208 VBA\Rimando_Input_Box.docm"

'prompts the user to select either Yes or No
 MsgBox Prompt:="Close document?", _
     Buttons:=vbYesNo
'If the user selects the Yes button then the document closes without making save changes.
 If vbYes Then
     Application.ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
 Else
     'Keep the document open
 End If

End Sub

K.Dᴀᴠɪs · Accepted Answer · 2018-12-15 05:18:26Z

3

That would be because you didn't return your msgbox's value to a variable.

In this case, I would prefer not to use a variable but just a simple Select Case statement:

    Select Case MsgBox(Prompt:="Close document?", Buttons:=vbYesNo)
    Case vbYes
         Application.ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges
    Case vbNo
         'Keep the document open
    End Select

But I did notice another issue, you declared docX as type documents, you probably meant to use Dim docX As Document (no s). And then I also noticed that you didn't even use the variable.

See the following to correct those issues as well:

Sub b()

    Dim docX As Document
    Set docX = Application.Documents.Open( _
           Filename:="C:\Users\johnr\OneDrive\Documents\CIS 208 VBA\Rimando_Input_Box.docm")

    Select Case MsgBox(Prompt:="Close document?", Buttons:=vbYesNo)
    Case vbYes
         docX.Close SaveChanges:=wdPromptToSaveChanges
    Case vbNo
         'Keep the document open
    End If

End Sub

edited Dec 15, 2018 at 5:18

answered Dec 15, 2018 at 5:11

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigBen Over a year ago

Awww I was going to propose If MsgBox(Prompt:="Close document?", Buttons:=vbYesNo) = vbYes Then... :)

K.Dᴀᴠɪs Over a year ago

Ha! - sorry :-)

Collectives™ on Stack Overflow

VBA MsgBox Yes/No

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related