CODEWITHSUNDEEP
excelvba
Frank M
Frank M

Reputation: 83

Preventing a workbook from closing

I have VBA to copy data from one sheet to another. After copying done a message pops up, asking if there "is any thing else?". What is supposed to happen is that when you say no, it saves and closes. If you say yes, it is supposed to save & go to a specific sheet. What is happening is that it saves & closes on both Choices. I know something is missing, but I am not quite certain what.

My code

Msg = "Hey!!! Copying complete!! Any Thing Else?"

Ans = MsgBox(Msg, vbYesNo)

Select Case Ans

    Case vbYes             
        Sheets("Referrals").Select

    Case vbNo
        GoTo Quit:

End Select

Quit:         
ActiveWorkbook.Close

End Function

Upvotes: 0

Views: 224

Answers (1)

tjb1
tjb1

Reputation: 757

Move the close event into vbNo, the way it is now is causing it to run no matter what is selected in the case.

Msg = "Hey!!! Copying complete!! Any Thing Else?"

Ans = MsgBox(Msg, vbYesNo)

Select Case Ans

    Case vbYes             
        Sheets("Referrals").Select

    Case vbNo
        ActiveWorkbook.Close SaveChanges:=True

End Select

End Function

Upvotes: 2

Related Questions