Working with popup boxes and VBA

[Ranger_j]

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

 

[Smitty]

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]

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
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]

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
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.