WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA MsgBox (Yes/No)

VBA MsgBox (Yes/No)

Excel VBA MsgBox (Yes/No)

In VBA, using the message box we can create a yes no msgbox which is used to record user input based on the click on yes or no, the syntax to make a yes no message box is as follows variable = MsgBox(“Text”, vbQuestion + vbYesNo + vbDefaultButton2, “Message Box Title”) where variable must be declared as an integer.

Often in VBA coding, we need to collect the input values from the users to perform some tasks and one of such tasks to collect the Yes or No response from users. By using VBA MsgBox Yes No method, we can write the code to proceed further in the code.

In certain situations, we may need to present a Yes or No option in front of the user to give their response, and based on that response. We can actually run the VBA code.

For example, look at the below image of the MsgBox in VBA.

VBA Message Box Continue.png

If the user says Yes, “we can write code to perform a specific task,” and if the user says “No,” we can write code to perform another set of tasks.

How to Work with MsgBox Yes/No Response?

You can download this VBA Message Box Yes or No Excel Template here – VBA Message Box Yes or No Excel Template

Example #1 – Copy and Paste based on Response

For example, look at the below code.

Code:

Sub MessageBox_Yes_NO_Example1()

 Dim AnswerYes As String
 Dim AnswerNo As String

 AnswerYes = MsgBox("Do you Wish to Copy?", vbQuestion + vbYesNo, "User Repsonse")

 If AnswerYes = vbYes Then
   Range("A1:A2").Copy Range("C1")
 Else
   Range("A1:A2").Copy Range("E1")
 End If

End Sub

VBA Message box Example 1

Explanation:

The above has declared the variable as String i.e.

Dim AnswerYes As String

In the next line, we have assigned the value through a message box asking, “Do you wish to copy?”.

AnswerYes = MsgBox("Do You Wish to Copy?", vbQuestion + vbYesNo, "User Repsonse")

Now IF statement evaluates the response given through the message box. If the message box result is vbYes then it will copy the range A1 to A2 and paste in cell C1.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
   If AnswerYes = vbYes Then
      Range("A1:A2").Copy Range("C1")

If the response given by the message box is No, then it will copy the range A1 to A2 and paste in cell E1.

Else
Range("A1:A2").Copy Range("E1")
End If

Ok, I have entered few values in cell A1 and A2 now.

VBA Message box Example 1-1

Now I will run the code using the F5 key, or through the run option, a message box will appear in front of me and asks for my response.

VBA Message box Example 1-2

If I click on Yes, it will copy the range A1 to A2 and paste in C1 cell. Now I will click on Yes and see the result.

VBA Message box Example 1-3

So it has performed the task assigned if the response is YES.

Now again, I will run the code.

yes or no Example 1-4

This time I will select No and see what happens.

yes or no Example 1-5

Yes, it performed the task assigned in the code i.e.

Else
Range("A1:A2").Copy Range("E1")

Example #2 – Hide & Unhide Sheets Based on the Response

The below code will hide all the sheets except the active sheet if the response is yes.

Code:

Sub HideAll()

  Dim Answer As String
  Dim Ws As Worksheet

  Answer = MsgBox("Do you Wish to Hide All?", vbQuestion + vbYesNo, "Hide")
  
  If Answer = vbYes Then

    For Each Ws In ActiveWorkbook.Worksheets
     If Ws.Name <> ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden
    Next Ws

   ElseIf Answer = vbNo Then MsgBox "You have selected not to hide the sheets", vbInformation, "No Hide"
   End If

End Sub

The above code will hide all the worksheets except the sheet we are in right now if the response from the message box is YES.

yes or no Example 2

If the response from the message box is NO, it will display the message box saying, “You have selected not to hide the sheets.”

yes or no Example 1-6

Similarly, the below code will unhide the sheet if the response is Yes.

Code:

Sub UnHideAll()

  Dim Answer As String
  Dim Ws As Worksheet
 
  Answer = MsgBox("Do you Wish to Unhide All?", vbQuestion + vbYesNo, "Hide")

  If Answer = vbYes Then

    For Each Ws In ActiveWorkbook.Worksheets
       Ws.Visible = xlSheetVeryHidden
  Next Ws

 ElseIf Answer = vbNo Then MsgBox "You have selected not to Unhide the sheets", vbInformation, "No Hide"
 End If

End Sub

This works exactly the same as the hide sheet code; if yes, it will unhide. If no, it will not unhide.

Recommended Articles

This has been a guide to VBA Message Box. Here we discuss how to create Yes or No response in the excel VBA using MsgBox along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • VBA Find Next
  • VBA ISERROR
  • VBA Collection Object
  • VBA Text Function
  • VBA Save As
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Message Box Yes or No Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More