I'm sure this is probably an elementary question, but since I'm new to this programming thing, I don't mind. I'm having trouble with an input box and a message box when Cancel or No is clicked.
From my input box, if cancel is clicked, I want to exit the sub. Here is my input box, but how do I create an if..then to find out if Cancel was clicked and then stop running the macro
myLPS = InputBox(msg, title, vbOKCancel)
Second thing, I have a message box that asks if the user wants to continue with Yes & No buttons. But, if I click No, it just keeps executing. I want it to execute from the end of an if..then if No is clicked. Here is the function called and my if..then:
If CheckOK(MLSName) = vbNo Then
GoTo ClickNo ' this is at the end of this big if statement - ClickNo:
Public Function CheckOK(MLS As String) As VbMsgBoxResult
Dim message As String
Dim title As String
message = "Create Task for: " & MLS & " "
title = "Continue..."
Result = MsgBox(message, vbYesNo, title)
End Function
I'm sure it's something easy, but I've been staring at code way too much in the past couple of weeks. Thank you in advance for any help.
InputBox & MsgBox button handling
MaheshS
It doesn't look like your function is returning a value. Add the line "CheckOK = Result" (or "Return Result" if you're using .NET) just before the End Function line. Also, you could get rid of the GoTo statement (and the Else clause) entirely if you changed it to "If CheckOK = vbYes" and then just put the code that you want to execute under that condition in the If block.
As far as the InputBox thing:
myLPS = InputBox(msg, title, vbOKCancel)
If Len(myLPS) = 0 Then Exit Sub
HTH,
Dave
David Gwynn-Jones
Someone correct me if I'm wrong, but I'm fairly certain that there is no way to programmatically check whether the user of an input box pressed cancel or left the input box empty and clicked ok because both result in an empty string(if there is a way to do this, I would love to know about it).
-Dave
Andew Foster
I don't know much about it, but I would assume, that you could somehow call a function AS vbMsgBoxResult that would return whether Ok or Cancel was clicked (ie. if result = vbCancel...), but my problem there is getting the function to also set my inputBox variable to whatever is input. (FROM BELOW) I would like to have the function set myInputBox to the input, and another "result" variable to vbOK or vbCancel. I'm dying here. This is all I really have left to do on this macro, and I'm stumped.
I can get:
myInputBox = InputBox(msg,title, vbOkCancel) ' to set myInputBox to whatever is in there, but when I tried something like
if myInputBox = vbCancel ' I get a type mismatch
or
if myInputBox.Result = vbCancel ' I get a compile error. I don't know if there is something like this if statement that I could do or not. Maybe I'm calling the wrong attribute, or maybe myInputBox doesn't get any attributes.
Rafael Oliveira
Thank you for the help on the message box.
As far as the InputBox answer:
I could do that, except myLPS can = "" and I would still want to execute. The input box is for additional exceptions. I already have two variables as exceptions that will run through the macro even if myLPS is empty. So, I need to have the macro run even if myLPS is empty when the user clicks "OK" but if the user clicks "Cancel" then I don't want to run at all. Do I need to do some error handling I don't really know how to do that or what to do in my EH function if needed.
Hammad Mohib
This all looks very VB6ish, to me.
As such, I believe that's one of the hokey things with the Input box - you can't really detect the cancel. If you must detect the cancel, 'roll your own', or use the default value setting and assume a zero length string as cancel.
The final solution is to stop using the input box
.
StevieG
If CheckOK returns "yes" and the code continues with the Else after GoTo ClickNo, will ClickNo: cause it to stop or will it ignore the placeholder ClickNo: and continue running the next statements
Here's the thing: All my work is inside a while loop and if CheckOK returns "no" then I still need to increment my reference variable and clear the other variable names. But, if it returns "yes" then I need to run the rest of the statements before ClickNo: and run the statements after. Seems like it should, but after reading about error handling with these codemarkers ending with a colon, I'm just not sure.
While condition=true
If CheckOK ...then
GoTo ClickNo
Else
Execute Code
Continue executing
End if
ClickNo:
r = r + 1
VariableName = Nothing
2ndVariable = ""
Wend