Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners

Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners

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)

The Takeaway

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

Downloads

Download the following workbook to get the proposed function and illustrations:

Download

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.

10 Comments

  1. Hey Ejaz,
    It’s really nice thing to ask user opinion before running code.

    I have small question. Can you please tell me which plugin you used for VBA code. It looks beautiful. So I can also use it on my blog.

    Thanks,
    Utkarsh

  2. HI, the idea is good and yes, the user must be aware of what he or she is doing.
    I have ‘specialized’ myself in writing Custom and Interactive Excel userinterfaces for many purposes. In many cases if a non-embedded Macro file is used I do this through AddIn files written for that purpose with custom ribbon menus and more of the sort.
    In these cases asking the user’s consent may be an overkill but I still like your idea.

    • Ejaz

      I design my own custom ribbons too, and I have encountered some instances where I needed to get the user’s “consent”. For instance, I have a macro to quickly delete all locally scoped named ranges in a sheet. And, most of my team members work on remote desktops and encounter screen lags often. This button got inadvertently clicked few times. Large amount of time was spent either re-naming all the ranges, or working back all changes from an older version of the model. Either way, it is good to just make sure if a user meant to click a button, if reversing the actions performed by a macro is time consuming.

      I have to admit I use this more like a oops-I-did-not-mean-to-click-that-stopper than a consent-getter.

      • True, very true. For these actions I have a separate Maintenance module with two different access levels.
        Since I include a configuration file there are sections that are accessible to the ‘normal’ user but entering the ‘development mode’ the process goes deeper and yea in that case the tasks are ‘consciously’ executed and weighed, but, there is always one that needs more attention, and yes again, in thous cases an extra confirmation is required.
        In some cases I have include a roll-back module using a built-in fail-safe.

        • Ejaz

          I use the two modes too. I call it the “Edit” and the “View” mode. I am curios about the roll back feature you speak off. How do you do it?

          • This I only do if the data is not too much and also on what it is.
            In some cases if it’s a record or a range mainly within one and the same sheet as create a temp sheet to store a copy if that sheet and sometime I ven make a copy of that sheet in the in the default %TEMP% folder, just in case. After the process has run and the x-checks are okay
            A button to Accept the changes and if so the roll-back is deleted to avoid errors.
            Not a great roll-back like Databases but it works and sunce sytems are not that slow the user hardly notices this.

          • Ejaz

            Sounds nice. How do you save a sheet in the temp folder? Do you save a new workbook? Or is there a cleverer way to do it?

          • This is a quick-and dirty routine to save tyhe active worksheet as workbook in the %TEMP% folder

            Public Sub Save2Temp()
            Dim Current As Workbook
            Dim FileName As String
            Dim WB  As Workbook
            Set Current = ThisWorkbook
            FileName = ActiveSheet.Name & ".xlsx"
            On Error Resume Next
            Kill Environ$("TEMP") & "\" & FileName
            On Error GoTo 0
            ActiveSheet.Copy
            ActiveWorkbook.SaveAs FileName:=Environ$("TEMP") _
                & "\" & FileName
            Set WB = ActiveWorkbook
            Current.Activate
            WB.Close False
            Set WB = Nothing
            End Sub
            

Comments are closed