Working with popup boxes and VBA


New Member
Jul 1, 2008
I have a macro that prompts for a password to unprotect a sheet and once entered, does a bunch more stuff. If the wrong password is entered is gives an error message and cancels the rest of the routine as it should. However, if I hit the 'cancel' button it goes on ahead with the routine which it is NOT supposed to do. I don't know syntax for popup boxes.

How do I code it so if someone clicks 'cancel' it exits the subroutine?

Probably a dumb question but I'm dumb when it comes to Visual Basic

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.


May 15, 2003
How about posting the code you've got so far?

This may give you a start in the meantime:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> pword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>        pword = InputBox("Please enter a password to proceed", "Password Required", "*******")<br>        <br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> pword<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = ""<br>                MsgBox "Cancelled"<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">Case</SPAN> "bigdog"<br>                MsgBox "Proceed"<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <> "bigdog"<br>                MsgBox "Wrong!"<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Tom Urtis

MrExcel MVP
Feb 10, 2002
A null string condition with the inputbox won't necessarily mean Cancel was hit; OK could have been hit with nothing entered.

Syntax for InputBoxes:

Instead of using the Application.InputBox method when trapping Cancel, it is do-able with the function InputBox, as the example below demonstrates using strptr:

Sub InputBoxExample1()
Dim ans1$
ans1 = InputBox("Please enter the name:", "Name")
Select Case True
Case StrPtr(ans1) = 0
MsgBox "You hit Cancel.", 48, "Entry cancelled." Exit Sub
Case Len(ans1) = 0
MsgBox "You hit OK but entered nothing.", 48, "Entry scuttled." Exit Sub
Case Else
MsgBox "You entered ''" & ans1 & "''.", 64, "OK with entry"
End Select
End Sub

If you have your heart set on using the Application method, this is an example of that:

Sub InputBoxExample2()
Dim CancelTest As Variant
CancelTest = Application.InputBox("Enter a value, or click Cancel to exit:")
If CancelTest = False Then
MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
Exit Sub
CancelTest = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
MsgBox "You entered " & CancelTest & ".", 64, "Please click OK to resume."
End If
End Sub


New Member
Jul 1, 2008
Thanks for the suggestions. I don't know if it's because I'm using Excel 2003 or what but all those suggestions give me ambiguous compile errors. What I had before brings up a default Excel input box for entering a password to unprotect a sheet. That is, I don't specifically call an input box. The code is:

Sub Example()
Sheets("Data Calculations-General").Select
End Sub

By not supplying the password to unprotect, it forces the user enter it to proceed, but like I say, if Cancel is hit it goes ahead with the code anyway. Basically, I need to do something like:

If 'Value'=False Then Exit Sub

But I have no idea what 'Value' is since I don't assign the variable and provide the box.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back