Excel VBA message box yes no tutorial

posted in: Excel Tutorials | 0

Excel VBA message box yes no tutorial

excel tutorials for dummies

Excel msgbox yes no outline

In this guide we will outline how you can add a VBA message box to your Excel macros which will give the user of the macro a dialogue box which will give them the option to proceed or not with a Yes/No selection. You can also specify a message to the user, which could for instance give them an outline of what will happen if they run the macro, or a warning of some sort if your macro will make changes that are irreversible for instance. This vbyesno message box is therefore very useful if you are sharing your spreadsheets with other users who may not be aware of the actions a certain macro will perform if executed, especially when there are more than one macros in a workbook.

VBA message box yes no guide

If you are completely new to Excel macros then please visit the beginners guide to get started. This guide will assume that you have created a macro already up to a certain point and are wishing to add this vbyesno feature to your own code.

Adding the VBA message box yes no feature

To do this you will need to open up your workbook and go to the “Developer tab” from there you will need to enable design mode and open up your macro code such as the image below.

excel vba message box yes no

From here you will need to enter the vba message box yes no code. I am going to firstly explain how this works. The code is split into 2 parts, which can simply be described as “what to do if the user clicks yes” and “what to do if the user clicks no”. The Excel msgbox yes no code with no modifications appears as below.

 

Private Sub CommandButton1_Click()

YesNo = MsgBox(“”, vbYesNo)

Select Case YesNo

Case vbYes

Case vbNo

End Select

End Sub

In the above example, clicking either yes or no would actually do nothing as there is no code attributed to the Yes command or the No command. So let’s put this code into action.

In our example which can also be seen in the video tutorial, we are going to insert a new row and apply gridlines.

We want our vba message box to display the following test to the user “This Macro will insert a new row, are you sure you wish to continue?”.  If the user clicks yes we want this action to perform, if the user clicks no we want nothing to happen.

The code that we will use to insert a new row and format the new gridlines is as follows.

 

 

Sheets(“Sheet1”).Range(“A4”).Select

ActiveCell.EntireRow.Insert shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select

Selection.Borders.Weight = xlThin

We will now show you how our code and message fits into our msgbox yes no vba code. The edits are shown in bold italics.

 

Private Sub CommandButton1_Click()

YesNo = MsgBox(“This Macro will insert a new row, are you sure you wish to continue?“, vbYesNo)

Select Case YesNo

Case vbYes

Sheets(“Sheet1”).Range(“A4”).Select

ActiveCell.EntireRow.Insert shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select

Selection.Borders.Weight = xlThin

Case vbNo

End Select

End Sub

 

 

You can see that as we want nothing to occur if the users selects “No”, there is no code underneath “Case vbno”. If you did want something to occur if the user said no you would paste your macro code between “Case vnbo” and “End Select.”

Excel VBA message box Yes No Video Tutorial

This concludes our Msgbox Yes No tutorial.

More tutorials can be located at the YouTube channel.

Leave a Reply