I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this post, we will look at how to get the user’s permission before running a procedure, using the MsgBox function.
If you are not a beginner, feel free to skip ahead to the part where I propose a handy function, which will return a Boolean variable, indicating the user’s choice.
I am sure you have already used MsgBox as a routine to display messages.
Sub Example1() MsgBox "I love Struggling To Excel!" End Sub
Now let us look at how to use MsgBox as a function, to get a Yes or No response from the user.
Sub Example2() 'I like Declaring Variables Very Explicitly Dim Answer As VbMsgBoxResult 'Get the Response Answer = MsgBox("Are you really sure you want to do this?", _ vbYesNo + vbQuestion) 'Act based on the Response If Answer = vbYes Then MsgBox "The user is sure.", vbInformation Else MsgBox "The user is not sure.", vbCritical End If End Sub
Did you know that you can combine two Message Box Styles using the “+” symbol? (Line 8 above)
You may have to take the user’s consent before running many of the procedures that you write. While explicitly typing out all the lines from the above example is not really cumbersome, it is not very ideal either. Therefore, I propose we outsource the logic to a little function.
Function AreYouSure(Optional ByVal Prompt As String) As Boolean Dim MsgButton As VbMsgBoxResult AreYouSure = False If Len(Prompt) = 0 Then Prompt = "Are you really sure you want to do this?" End If Prompt = Prompt & vbNewLine & vbNewLine & _ "Click Yes to continue." MsgButton = MsgBox(Prompt, vbYesNo + vbQuestion) AreYouSure = CBool(MsgButton = vbYes) End Function
Using the above function, we could rewrite Example-2 as follows:
Sub Example3() 'I like Declaring Variables Very Explicitly Dim Answer As Boolean 'Get the Response Answer = AreYouSure("Do you want to run this macro?") 'Act based on the Response If Answer Then MsgBox "The user wants to.", vbInformation Else MsgBox "The user does not want to.", vbCritical End If End Sub
The Ultimate Takeaway
You could also consider adopting the following strategy to quickly stop executing a macro.
Sub Example4() If Not AreYouSure Then Exit Sub ' End Sub
Download the following workbook to get the proposed function and illustrations:
Happy New Year Readers!
It’s a new year, and I thought I’d start the year by remembering something every developer should code into their programs: Compassion. If you are reading this article, chances are, you are automating a process; creating that omnipotent ‘Launch’ button which would probably steal peoples’ jobs. The least you could do is get their consent before running your macro, don’t you think?
Do let me know if you found this post useful.