Working with popup boxes and VBA

Ranger_j

New Member
Joined
Jul 1, 2008
Messages
21
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
Joined
Feb 10, 2002
Messages
11,224
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:

Code:
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:

Code:
Sub InputBoxExample2()
Dim CancelTest As Variant
showInputBox:
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
ElseIf
CancelTest = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
MsgBox "You entered " & CancelTest & ".", 64, "Please click OK to resume."
End If
End Sub
 

Ranger_j

New Member
Joined
Jul 1, 2008
Messages
21
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:

Code:
Sub Example()
Sheets("Data Calculations-General").Select
ActiveSheet.Unprotect
~~code
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,140
Members
414,214
Latest member
marketingnumbersguy

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 MrExcel.com.
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 "mrexcel.com".
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
Top